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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mll92
Frequent Visitor

Create a table of values over last several days

Hello,

 

I need to create a five-day log showing overall system status each day. Changes to a status may be made multiple times a day, or may not be made at all. As an example, I have the following table:

 

Date                                 System      Rating   Rating#

12/15/2022 11:50 a.m.MicrosoftGood

1

12/15/2022 2:00 p.m.MicrosoftOkay

2

12/16/2022 8:00 a.m.FacebookBad

3

12/17/2022 1:00 p.m.MicrosoftGood

1

12/18/2022 1:00 p.m.FacebookGood

1

 

What I need to do is return either the "worst" (max) rating per day (3 being the max) or, if there is no value entered for the day, then I need the last value entered for that category. 

 

Therefore, my tracker should look like this: 

 12/19/202212/18/202212/17/202212/16/202212/15/2022
MicrosoftGoodGoodOkayOkay 
FacebookGoodBadBadBad

Okay

 

I haven't been able to figure this out. Any suggestions?

2 REPLIES 2
johnt75
Super User
Super User

If you have a date table and the date & time stored as separate columns in your fact table you can use

Daily Rating =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR CurrentRating =
    SELECTCOLUMNS ( TOPN ( 1, 'Table', 'Table'[Rating#] ), "@val", 'Table'[Rating] )
VAR PrevRating =
    SELECTCOLUMNS (
        CALCULATETABLE (
            TOPN ( 1, 'Table', 'Table'[Date], DESC, 'Table'[Time], DESC ),
            'Date'[Date] < CurrentDate
        ),
        "@val", 'Table'[Rating]
    )
RETURN
    COALESCE ( CurrentRating, PrevRating )
mll92
Frequent Visitor

Thanks! How would I go about changing this to show the "worst" status if the status changes after midnight? For example, if Microsoft is "Bad" until 12:30 a.m. then changes to "Okay," the dashboard needs to show "bad" for the day because that is the worst status for the day. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors