Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MalOSB
Frequent Visitor

Not in the last 3 days

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

SiteNameLatest Created
Leeds01/02/2021 06:10:57
Manchester01/02/2021 15:32:27
Liverpool01/02/2021 06:49:53
Glasgow01/02/2021 04:37:55
London21/01/2021 09:00:03
Watford30/01/2021 06:54:32
Portsmouth01/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

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@MalOSB 

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)

 

 1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ryan_mayu
Super User
Super User

@MalOSB 

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)

 

 1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
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))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.