Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Date Filter in Measure not working properly

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!
Need Help

3 Replies

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    Hi bao_nguyen,

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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!