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.
I am new to Power BI and hoping someone can help understand the reasoning and the correct way to do this.
The sample PBIX file is available here: https://ufile.io/iw27ae44
1. The measure M1 is the original measure which works fine (responds to filter) when I use a filter through a slicer which uses a related table.
2. The measure M2 FAIL is a scenario, where I am trying to do a mathematical operation (Example: 1 - CALCULATE RESULT = some %) on the result of the CALCULATE function. The measure yeilds correct results, but the data filter stops working.
3. To make it work, I had a create a measure M2 PASS using column M2 Value with a hard coded, so that the CALCULATE doesn't loose context. It correctly corresponds to the date slicer.
My questions are:
1. Why does it loose context when I add a + or - operator. It seems to work with a * operator.
2. How I can achieve the M2 PASS result in an elegant / recommended way.
Thanks in Advance
Andy!
Solved! Go to Solution.
Becasue your Dates table is joined to your Data table you don't need to use RELATED in your measure, M1 can just be
M1 = SUM(Data[Sales])
Now, for the context on M2, it's not losing it it's just the fact that each date exists so it is able to calculate the 1 on every row. If you only want to show it on rows where M1 is not blank you can do it like so.
M2 =
VAR _M1 = [M1]
RETURN
IF ( NOT ISBLANK ( _M1 ), _M1 - 1, BLANK())
Becasue your Dates table is joined to your Data table you don't need to use RELATED in your measure, M1 can just be
M1 = SUM(Data[Sales])
Now, for the context on M2, it's not losing it it's just the fact that each date exists so it is able to calculate the 1 on every row. If you only want to show it on rows where M1 is not blank you can do it like so.
M2 =
VAR _M1 = [M1]
RETURN
IF ( NOT ISBLANK ( _M1 ), _M1 - 1, BLANK())
Another way to do it is to use a trick of DIVIDE returning a BLANK on error and BLANK * <anything> is BLANK.
M2 V2 =
VAR _M1 = [M1]
RETURN
DIVIDE( _M1 , _M1 ) * ( _M1 - 1 )
We use [M1] 3 times but because we used a VAR it only gets pulled by the DAX engine once.
@andy_pat , addition can force a left join. to avoid that try like
M2 FAIL = sumx(Data,
1-SUM(Data[Sales])
)
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 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |