Date Filter in Measure not working properly

[bao_nguyen]

Hello,

I have a table with columns Date and Failure Mode. I wrote a measure to calculate the passing rate in the last 30 days.

30 Day Yield =

VAR __pass = COUNTROWS(

    FILTER(

        'tbl',

        'tbl'[Failure Mode] == "PASS" &&

        'tbl[Date] >= TODAY()-30

    )

)

VAR __fail = COUNTROWS(

    FILTER(

        'tbl',

        not 'tbl'[Failure Mode] == BLANK() &&

        not 'tbl'[Failure Mode] == "PASS" &&

        'tbl[Date] >= TODAY()-30

    )

)

RETURN ROUND(IF(ISBLANK(DIVIDE(__pass,__pass+__fail)*100),0,DIVIDE(__pass,__pass+__fail)*100),0)

This returns the correct value but for the whole table instead of just 30 days. What interesting is when I add a slicer for relative date, it returns what I expected.

Please help!

Thanks!

[v-huijiey-msft]

Hi @bao_nguyen ,

Thanks for the reply from Wilson_ .

When you add a slicer for a relative date, it changes the filter context of the entire report page or visual, which is why you see the expected results when applying a slicer.

For more information on how filter context works see:

Filter context in DAX - SQLBI

If you want to implement your needs through DAX syntax, please provide me with the pbix file. Remember not to log in to your account on Power BI Desktop when uploading the pbix file.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[bao_nguyen]

Hi Yang,

Thanks for the info. I am slowly reading through the link you provided. 

For now, I was able to get it to work by adding a date filter to the visual (card) but definitely not ideal.

I can't share my pbix file due to company policy but I can share a snippet of the data if that works?

Thanks!

[Wilson_]

Hi bao_nguyen,

Can you please share screenshots of your visual and/or model too?