cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodneyc8063
Helper IV
Helper IV

DAX CALCULATE - Using only Measure Expression with NO filter - How does it work?

So I have been trying to learn/study Power BI and "thought" I had a good decent grasp on DAX and CALCULATE that is UNTIL I came across a sample question that totally threw me for a loop.

 

I am going through the Microsoft Power BI Official Practice Exam from Measure Up currently. 

 

A question states that we need to create visualizations based on some financial data (Here is a sample excel file from Google Drive)

 

We need to create a measure for the average profit for products for use in visuals. We need to ensure that the measure you create will behave correctly when used with the CALCULATE functions. 

 

Its a drag and drop question that asks you to write a piece of DAX code - The answer is as follows

 

Average Product Profit = 

AVERAGEX (

      KEEPFILTERS(VALUES('financials'[Product])),

      CALCULATE(AVERAGE('financials'[Profit]))

)

 

So I am confused about a few things by the code above that I was hoping for some help to understand

 

For the line: [ KEEPFILTERS(VALUES('financials'[Product])) ]

 

I was under the assumption that KEEPFILTERS would only be used within CALCULATE.

 

So I usually see [ CALCULATE (<MEASURE>,KEEPFILTERS<filter>) ] 

 

I tried to play around with the data in PBI and remove the KEEPFILTERS and only keep the "VALUES('financials'[Product])" and the output seems to be the same. So Im not sure why KEEPFILTERS is even needed here?

 

Secondly, my biggest confusion is with the CALCULATE portion of the formula as shown

 

CALCULATE(AVERAGE('financials'[Profit]))

 

Usually again, I am used to seeing CALCULATE(<Measure>, <Filters>.......)

 

I havent seen JUST the CALCULATE(<Measure>) on its own

 

Based on the Microsoft documentation (https://docs.microsoft.com/en-us/dax/calculate-function-dax) I see that it is possible to only use CALCULATE(<Measure>), but the documentation isnt clear to me as to why or how to use it like so. 

 

Again I tried to play around with the data in Power BI and I couldnt quite wrap my head around why or when to use JUST the CALCULATE function with no filters?

 

Sample .PBIX file

 

For example, I tried to create two measures

AVG_1 = AVERAGE(Sheet1[Profit])
CALC_1 = CALCULATE(AVERAGE(Sheet1[Profit]))
 
As can be seen in the screen shot below it doesnt seem to make any difference either using CALCULATE with AVERAGE or just AVERAGE
 
rodneyc8063_0-1617993945187.png

 

Then I tried to use the formula exactly as shown

 

rodneyc8063_1-1617994033425.png

 

Right beside the same formula but without using the CALCULATE

 

rodneyc8063_2-1617994086316.png

 

Each row total looks the same, the difference comes in the row total.

 

It looks like for the column "Average Product Profit" its taking the average of the values shown

 

So -> 247500+137020.75+142091.88+90540+113294.42=3459263/5=146089.41 - Which is basically an average of the 5 products. 

 

Then "Average Product Profit 2" seems to be taking [ sum of all values / 13 = 1794373.75/13 = 138028.75 ] -> Which is the total number of values. 

 

So using CALCULATE in isolation as a column measure doesnt seem to do much. But when using CALCULATE in an iterator function (AVERAGEX) it seems to change things a bit?

 

I just cant quite wrap my head around what is going on exactly

 

Any insight or advice would be very much appreciated

1 ACCEPTED SOLUTION
jdbuchanan71
Super User II
Super User II

@rodneyc8063 
The KEEPFILTERS(VALUES('financials'[Product])) doesn't really makes sense to me either, that one seems odd.

 

The CALCULATE inside the iterator forces context transition so the row context (the row being interated) is converted into a filter context.  Take a look at this article that discusses the topic.
https://www.sqlbi.com/articles/understanding-context-transition/

 

View solution in original post

4 REPLIES 4
jdbuchanan71
Super User II
Super User II

@rodneyc8063 
The KEEPFILTERS(VALUES('financials'[Product])) doesn't really makes sense to me either, that one seems odd.

 

The CALCULATE inside the iterator forces context transition so the row context (the row being interated) is converted into a filter context.  Take a look at this article that discusses the topic.
https://www.sqlbi.com/articles/understanding-context-transition/

 

View solution in original post

That use of KEEPFILTERS looked strange to me too but that usage is explained here.

 

https://www.sqlbi.com/articles/keepfilters-a-new-dax-feature-to-correctly-compute-over-arbitrary-sha...

Ah, very nice.  Count on the Italians to have covered the topic!

Thanks @jdbuchanan71 and @AlexisOlson  - This is super helpful!

 

But also looks like I have quite a bit of homework this weekend to read over 😞

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors