Debugging DAX is difficult because unlike excel you cannot use the F2 Key to see the data that is being fed into the DAX Code.
I recently did a video on explaining Why is DAX Hard? In this post I’ll describe how can you use CONCATENATEX DAX function to debug DAX and take a look at the data that feeds into your DAX Calculation.
Start with this Video – Debug DAX using CONCATENATEX
Consider this simple Ranking Calculation
The data model below has a Products, Calendar and Sales tables in a one to many relationship.
And I have a pivot table below, with Year, Product and Total Sales displayed.
Next, I'd like to calculate the rank of each product in the order of total sales. Let’s create this simple Ranking measure.
Prod Rank = RANKX ( ALL ( Products[Product] ), [Total Sales], , DESC, DENSE )
And I get this output.
If you are familiar with RANKX, I am sure you'd also understand the reason of wrapping the products table in the ALL Function. Although the ALL function removes the filter from the Products table, but you still can’t literally see the table being fed inside the RANKX function.
Let’s play a little and make tweaks using CONCATENATEX.
Enters CONCATENATEX
CONCATENATEX is an excellent function to peak into the tables that feed into your DAX. Using this function, you can pull apart the DAX code in a systematic way to see what values are fed in the formula that drive a particular result.
Consider this measure using CONCATENATEX. The ALL ( Products [ Product] ) & [Total Sales] are the two inputs that we’ve taken from our previous rank function.
Explore Prod Rank = CONCATENATEX ( ALL ( Products[Product] ), -- remove the filter from the Product Name [Total Sales], UNICHAR ( 10 ) -- delimiting sales values in different rows )
Now see this output.
CONCATENATEX function concatenates all the sales values that were being captured in ALL ( Products [ Product] ) Function. Let’s take this further and calculate the Rank along with the Sales Values.
To see the rank is assigned to each sales value, I can revise my DAX something like this.
Explore Prod Rank = CONCATENATEX ( ALL ( Products[Product] ), -- remove filter from the Product Name [Total Sales] & ' | ' & RANKX ( -- calculating rank for each sales value ALL ( Products[Product] ), [Total Sales],, DESC, DENSE ), UNICHAR ( 10 ) )
Here is the output
The measure above explores the rank and sales values for each product. In summary we have been able to make the invisible working of DAX, somewhat visible.
Recently I spoke extensively about DAX Debugging using CONCATENATEX at the London Excel User Meetup.
More on DAX
- Find Missing Values in Lookup Tables
- Switch between YTD and Current Period Calculations
- Allocation Calculations in Power BI
- Weighted Average in Power BI
- Percentile Calculations in Power BI
- Slab Calculations in Power BI
