Indirect Filter using RELATED Table

[andy_pat]

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!

[jdbuchanan71 · Accepted solution]

@andy_pat 

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]

@andy_pat , addition can force a left join. to avoid that try like

M2 FAIL = sumx(Data,
 1-SUM(Data[Sales])
 )