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.
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. | Microsoft | Good | 1 |
12/15/2022 2:00 p.m. | Microsoft | Okay | 2 |
12/16/2022 8:00 a.m. | Bad | 3 | |
12/17/2022 1:00 p.m. | Microsoft | Good | 1 |
12/18/2022 1:00 p.m. | Good | 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/2022 | 12/18/2022 | 12/17/2022 | 12/16/2022 | 12/15/2022 | |
Microsoft | Good | Good | Okay | Okay | |
Good | Bad | Bad | Bad | Okay |
I haven't been able to figure this out. Any suggestions?
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 )
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.
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |