DAX CALCULATE Debugger

CALCULATE is the most powerful function in DAX, as it allows you to change the filter context under which its expression is evaluated to your hearts content. But with big number of options to choose from, often comes big frustration when the results don’t match expectations. Often this is because your syntax to modify the filter context doesn’t do what you’ve intended. Unfortunately CALCULATE only displays its result and not how it achieved it, so debugging becomes a challenge. This is where my CALCULATE Debugger measure can help out:

DAX CALCULATE Debugger

This is a measure that returns a text-value, showing the number of rows of the adjusted filter context table, the MIN and MAX value of the selected column as well as up to the first 10 values. Just place this measure beneath the CALCULATE-measure in question and try to find the error 😉

Just have in mind, that this only works for standalone CALCULATE-functions and not for those who are nested in other functions (who modify the filter context).

The YTD-measure is defined as follows:

YTD = CALCULATE ( [Amount], DATESYTD ( 'DimDate'[Datum] )

The code for the DAX Debugger measure looks like this:

In row 2 you fill in the filter expression from the YTD-expression (2nd argument: ‘DimDate'[Datum]). You can choose from which column the values shall be shown, just write that in rows 6, 7 and 11 ([Datum]). If you want to adjust the TOPN-figure for the sample values to be shown, replace the 10 in row 9 accordingly. If you don’t want to show sample values at all, just uncomment row 13 and comment out row 14 and 15.

Thanks to Tom Martens for providing the crucial hint of how to reference a column from a table that’s defined in a variable by using X-functions!

Further adjustments have to be made, if your filter expression uses the syntax sugar of boolean expressions like so:

CALCULATE ( [Amount], Product[Color] = "Red" )

This expression only returns a table when used as a filter argument in CALCULATE but not standalone in a DAX variable. So you’d have to translate the filter expression to the native underlying code like so:

FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" )

As this cries for some automation, I’ve produced some nifty M-function that does all that autoMagically. It lives in my M-function-library so I have it at hand within PowerBI for immediate use.

The M-function

This function creates the DAX-code in the query editor. Just fill in the parameters (see below) and the DAX code will be created automatically: Just copy and paste as a new measure.

How to fill the parameters:

  1. filterExpression: DAX-code of the CALCULATE filter expression
  2. myColumName. Name of the column whose values to show
  3. MaxFilter: This is a optional parameter: Fill in a different number from 10 if you want to change the default value for the TOPN selection of the sample values to be shown.

This function detects boolean expressions automatically and produces the appropriate code.

If you don’t know how to use M-function-code, please check out Ruth Pozuelo’s video.

Enjoy & stay queryious 😉

Export data from Power BI using Microsoft Flow

Edit 5th May 2019: Unfortunately this method will not work in the Power BI service!

In my last 2 posts I’ve described a way to automatically validate attachments from incoming E-mails. Microsoft Flow would watch for incoming E-mails, that match certain criteria and move their attachments to a dedicated folder. Then it would trigger a refresh of a Power BI dataset, that has been designed to check for errors in those attachments. Data driven alerts in Power BI would indicate if there are errors and trigger a Flow that sends an E-mail back to the sender, informing him that his attachments didn’t meet the agreed criteria.

In this article I will now explain how not just a trigger about the existence of a faulty attachment could be passed back to Flow, but also the corresponding data itself. Therefore I write a query that exports data from Power BI to Flow. But watch out: This is not suitable for very big tables. I experienced timeouts at tables with 300k rows already. Read more

Part 2: Automatically validate E-mail attachments with Flow and Power BI

In Part 1 of this little series I described the core-Flow on how to automatically validate E-mail attachments with Flow and Power BI. It automatically sends an e-mail to a business partner who sent an attachment, that didn’t meet the agreed specifications:

Automatically validate e-mail attachments – Part1

But before going live with this Flow, you should consider the following aspects:

Caveats

Read more