Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
For example, I tried to create two measures
Then I tried to use the formula exactly as shown
Right beside the same formula but without using the CALCULATE
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
Solved! Go to Solution.
@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/
@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/
That use of KEEPFILTERS looked strange to me too but that usage is explained here.
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 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |