Forum Discussion
Indirect Filter using RELATED Table
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!
Anonymous
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())
3 Replies
- jdbuchanan71
Super User
Anonymous
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())- jdbuchanan71
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.
- amitchandak
Anonymous , addition can force a left join. to avoid that try like
M2 FAIL = sumx(Data, 1-SUM(Data[Sales]) )
