Understanding DAX Formula

Course - Ultimate Beginners Guide to DAX examples . last section of the course

Customer Group Profits = 
CALCULATE( [Total Profits],
	FILTER( VALUES( Customers[Customer Name] ),
			FILTER( 'Customer Groups',
				RANKX( ALL( Customers[Customer Name] ), [Total Profits],, DESC ) > 'Customer Groups'[Min] 
				&& RANKX( ALL( Customers[Customer Name] ), [Total Profits],, DESC ) <= 'Customer Groups'[Max] ) )
			> 0 ) )

Could you please help in understanding the above formula step by step in detail as it very important for further DAX understanding

Do you have a specific question about this function? Sam does an excellent job of explaining what is going on in this formula.


Hi Nick,

have listed doubts for better understanding…

a) Role of countrows in the above DAX.
b) How is outer filter function getting connected with inner filter function.
c) What is the output of inner filter function.
d) RANKX( ALL( Customers[Customer Name] ), [Total Profits], DESC ) . Is ALL fetching list of unique result ,if yes isnt the customer name should have one value at the level as it is getting iterated from the outer filter function FILTER( VALUES( Customers[Customer Name] ),

I made some notes in the function itself, see if that helps:

Customer Group Profits =
/* Things to remember:
1. Generally better to look at the functions inside out, rather than outside in
2. FILTERS are iterators
3. Filters are tables

/* Aggregating the Total Profits happens at the very end*/
    [Total Profits],

/* We are going to filter the customer Names */
            VALUES( Customers[Customer Name] ),

/* You set a variable here for the customer rank to make the code easier to follow and probably a performance benefit too
but need to set the variable after you started iterating the Customers [Customer Name] column
Need to use ALL here to remove any filters from Customer Name, otherwise you will be comparing the current customer's total profit to itseld, which will always be one
            VAR __CustomerRank=
                RANKX( ALL( Customers[Customer Name] ), [Total Profits],, DESC )

/* This will return one row, the customer group, since the customer rank can only be in group. Could have easily been =1 instead of >0 
So as we iterate through each customer we "know" what customer group they belong to based on their rank*/
                        'Customer Groups',
				        __CustomerRank > 'Customer Groups'[Min]
				        && __CustomerRank <= 'Customer Groups'[Max]
                > 0


Thanks for the details, helps a lot in understanding

Is there any study material/blog/videos to understand context very thoroughly

The “Mastering DAX calculations” module would be a good place to start. Context appears to be pretty simple ( and in theory it is) but it becomes much more complex as you get deeper into DAX.