Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter data based on calculation

Hi All, I'm new to power bi, and need help with the following scenario. We are getting device data , which lists alarms with start date time and end date time. Sample data shown below, where users w...
Need Help
  • mwegener's avatar
    mwegener
    5 years ago

    Hi Anonymous ,

     

    try this.

    ShowInPeriod = 
VAR RangeStart =
    MIN ( DateList[Date] )
VAR RangeEnd =
    MAX ( DateList[Date] )
VAR Alerts =
    FILTER (
        'EDM Overflow Data',
        ( RangeStart <= 'EDM Overflow Data'[Startdate]
            && RangeEnd >= 'EDM Overflow Data'[StartDate] )
            || ( RangeStart <= 'EDM Overflow Data'[EndDate]
            && RangeEnd >= 'EDM Overflow Data'[EndDate] )
            || ( RangeStart >= 'EDM Overflow Data'[StartDate]
            && RangeEnd <= 'EDM Overflow Data'[EndDate] )
    )
RETURN
    COUNTROWS ( Alerts )



parry2k's avatar
parry2k
Super User
5 years ago

Anonymous try this measure and filter on 1

 

Measure = 
COUNTX ( 
    Range, 
    IF ( 
        ( Range[Start date of alarm(s)] <= MIN ( 'Calendar'[Date] ) || Range[Start date of alarm(s)] <= MAX ( 'Calendar'[Date] ) ) && 
        ( Range[End date of alarm(s)] <= MIN ( 'Calendar'[Date] ) || Range[End date of alarm(s)] <= MAX ( 'Calendar'[Date] ) ), 
        1 
    )
)

 

✨ Follow us on LinkedIn

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Parry2k, Thanks for the update. I had to tweak the if condition a little bit, but this expression is working. Currently we have reduced the developement data, so I can't comment on performance but this is working slightly better than my switch statement. 🙂