Filter data based on calculation

[Anonymous]

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 ID	Start date of alarm(s)	Start time of alarm(s)	End date of alarm(s)	End time of alarm(s)	Overflow Alarm Duration (Secs)
10585094	30/09/2020	18:46:40	02/10/2020	20:50:55	745500
10585094	01/10/2020	18:46:40	02/10/2020	20:50:55	74550
10585094	02/10/2020	18:46:40	02/10/2020	20:50:55	7455
10585094	03/10/2020	01:18:26	03/10/2020	14:15:10	46604
10585094	04/10/2020	09:28:09	04/10/2020	13:36:19	14890
10585094	13/10/2020	19:50:41	13/10/2020	20:43:10	3149
10585094	24/10/2020	19:30:39	25/10/2020	03:08:10	27451

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

[mwegener · Accepted solution]

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]

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]

Hi @Anonymous ,

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

[parry2k]

@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]

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