Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
Solved! Go to 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 )
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 )
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.
Hi @Anonymous ,
can you show the problem in screenshots?
It should work as a filter on the visual or not?
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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. 🙂
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |