Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to create a report to show sites that have not triggered an alarm in the last 4 days. The software SQL database holds all activities in a single Events Table. I created a table in PowerBI with the Site name and then the Created field showing the latest date, filtered so that only Alarms are showing. Then I need to only show the sites that have the last alarm older than 3 days, however, the date relative filters on dates are only for “in the last”, “is in this” or “is in the next”. Not the “not in the last” that I need. We have 1000’s of sites so I need to filter to only show the sites that have not generated an alarm.
Example table
SiteName | Latest Created |
Leeds | 01/02/2021 06:10:57 |
Manchester | 01/02/2021 15:32:27 |
Liverpool | 01/02/2021 06:49:53 |
Glasgow | 01/02/2021 04:37:55 |
London | 21/01/2021 09:00:03 |
Watford | 30/01/2021 06:54:32 |
Portsmouth | 01/02/2021 04:10:35 |
Looking online I found the following suggestion,
IF(Events[Created]>TODAY() && Events[Created]<TODAY()-3,1,0)
But this does not work, returning “0” for all sites. I believe because I need to filter for it to look at only the Alarms in the event table. I have tried to add a Filter but after spending a few hours looking online and fiddling with Filters in Dax I cannot get it to work.
Events Table
Events[Created]
Events[EventTypeID] – All alarms are 1, patrols are 2 ect..
GroupID – Link to site details table
Thank you for any help you can give.
Malcolm
Solved! Go to Solution.
There is something wrong with the logic of your formula. It is impossible that a date is bigger than today and smaller than past three days.
have you tried below DAX?
Column = if('Table'[Latest Created]<today()-3,1,0)
Proud to be a Super User!
There is something wrong with the logic of your formula. It is impossible that a date is bigger than today and smaller than past three days.
have you tried below DAX?
Column = if('Table'[Latest Created]<today()-3,1,0)
Proud to be a Super User!
@MalOSB , Create measures like
has trigger in last four days= calculate(countrows(Table), filter(Table, table[Latest Created] >=today()-4))
no trigger in last four days= if(isblank[has trigger in last four days],1,0)
has trigger in last four days sites count = countx(values(Table[SiteName]),if(isblank[has trigger in last four days],1,0))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |