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 want to see all alarms active in a date range which they can select. 

Point IDStart date of alarm(s)Start time of alarm(s)End date of alarm(s)End time of alarm(s)Overflow Alarm Duration (Secs)
1058509430/09/202018:46:4002/10/202020:50:55745500
1058509401/10/202018:46:4002/10/202020:50:5574550
1058509402/10/202018:46:4002/10/202020:50:557455
1058509403/10/202001:18:2603/10/202014:15:1046604
1058509404/10/202009:28:0904/10/202013:36:1914890
1058509413/10/202019:50:4113/10/202020:43:103149
1058509424/10/202019:30:3925/10/202003:08:1027451

Eg. if they select date range from 02/10 - 03/10 then the top 4 records should get displayed because these alarms are active b/w 02/10 & 03/10.


I have added a date table , joined on Start date in the fact table. On adding this date in slicer only the records which are in range of date selection are displayed. So, i have also created a date table which is not joined to any table and only used to input date range. After this I created a measure to check if alarm dates are in range, and which returns 1 if true as shown below:

ShowInPeriod =
var RangeStart = MIN(DateList[Date])
var RangeEnd = MAX(DateList[Date])
var showIf =
SWITCH(
TRUE(),
(RangeStart <= MIN('EDM Overflow Data'[StartDate]) && RangeEnd >= MIN('EDM Overflow Data'[StartDate]) ) ,1,
(RangeStart <= MIN('EDM Overflow Data'[EndDate]) && RangeEnd >= MIN('EDM Overflow Data'[EndDate]) ) ,1,
(RangeStart >= MIN('EDM Overflow Data'[StartDate]) && RangeEnd <= MIN('EDM Overflow Data'[EndDate]) ) ,1
)
return showIf
 
Then I put this measure in the filter pane and set filter to 1. In doing so I'm getting error, that visual has exceeded available resources.
 
Please advise regarding this scenario.
 
Kind Regards
Anshuman

 

 

 

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 )



5 Replies

  • 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. 🙂

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    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 )



    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Marcus,

       

      Thanks for your reply. The solution works better than the switch I was using. I also tweaked your expression slightly as given below, by removing the date filter from the date table which is joined to the fact. I thought with this I can get away without creating a second date list , as I have currently.

      ShowInPeriod4 =
      var RangeStart = MIN('Calendar'[Date])
      var RangeEnd = MAX('Calendar'[Date])

      VAR Alerts =
      FILTER (
      CALCULATETABLE('EDM Overflow Data',REMOVEFILTERS('Calendar'[Date])),
      ( 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 )
       
      This works similar in terms of timing with your expression, but I have to include this in the list else it works as a normal slicer would. Is there a way to keep this in the list, but hide it from view ??
      • mwegener's avatar
        mwegener
        Most Valuable Professional
        5 years ago

        Hi Anonymous ,

         

        can you show the problem in screenshots?
        It should work as a filter on the visual or not?