Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
The below is my sample dataset where getting values at every 5 minutes interval.
ICR | Inverter | SCB | Unit | Count of Anomaly | DateTime |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 7:17 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 7:27 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 8:19 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 8:24 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 9:21 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 9:31 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 9:42 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 10:15 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 11:36 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 15:18 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 16:20 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 17:17 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 17:27 |
ICR1 | INV1 | SCB10 | U1 | 1 | 1/17/2021 18:14 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 7:17 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 7:27 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 8:19 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 8:24 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 9:21 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 9:31 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 9:42 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 10:15 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 11:36 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 14:16 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 14:21 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 14:36 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 15:02 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 15:18 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 17:17 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 17:27 |
ICR2 | INV1 | SCB10 | U1 | 1 | 1/17/2021 18:14 |
ICR1 | INV1 | SCB10 | U2 | 1 | 1/17/2021 18:14 |
ICR2 | INV1 | SCB10 | U2 | 1 | 1/17/2021 18:14 |
I need to display the status of unit (based on count of anomaly) in matrix visual like below.
The below are the conditions to display the colours:
1.Green Colour: if the anomaly count is less than 10 for the current day
2. Orange Colour: if the anomaly count is equal or greater than 10 for the current day and the anomaly count for the any day in the last 6 days is less than 10
3. Red Colour: if the anomaly count is equal or greater than 10 for the current day and anomaly count for each day in the last 6 days is equal or greater than 10
Additional Condition: we have to conisder the values only for the time in between only 9:30 AM to 5:30 PM
Please help me to achieve this.
Thanks
Solved! Go to Solution.
Hi @jitpbi ,
Basically you can try to create this measure:
count =
VAR tab =
FILTER (
'Table',
TIME ( HOUR ( 'Table'[DateTime] ), MINUTE ( 'Table'[DateTime] ), SECOND ( 'Table'[DateTime] ) )
>= TIME ( 9, 30, 0 )
&& TIME ( HOUR ( 'Table'[DateTime] ), MINUTE ( 'Table'[DateTime] ), SECOND ( 'Table'[DateTime] ) )
<= TIME ( 17, 30, 0 )
)
VAR currentcount =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () ) )
VAR p1 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 1 ) )
VAR p2 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 2 ) )
VAR p3 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 3 ) )
VAR p4 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 4 ) )
VAR p5 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 5 ) )
RETURN
SWITCH (
TRUE (),
currentcount < 10, UNICHAR ( 128994 ),
currentcount >= 10,
IF (
p1 < 10
|| p2 < 10
|| p3 < 10
|| p4 < 10
|| p5 < 10,
UNICHAR ( 128992 ),
IF (
p1 >= 10
&& p2 >= 10
&& p3 >= 10
&& p4 >= 10
&& p5 >= 10,
UNICHAR ( 128308 )
)
)
)
in my sample it should be all green
Attached the sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yingjl ,
This is working for me and need to add one more condition in the code:
> There is one more field "Actual" in the dataset, so if "Actual" value is 0 then display with grey colour
Please help to add this logic also in the code you shared.
Thanks
Hi @jitpbi ,
Basically you can try to create this measure:
count =
VAR tab =
FILTER (
'Table',
TIME ( HOUR ( 'Table'[DateTime] ), MINUTE ( 'Table'[DateTime] ), SECOND ( 'Table'[DateTime] ) )
>= TIME ( 9, 30, 0 )
&& TIME ( HOUR ( 'Table'[DateTime] ), MINUTE ( 'Table'[DateTime] ), SECOND ( 'Table'[DateTime] ) )
<= TIME ( 17, 30, 0 )
)
VAR currentcount =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () ) )
VAR p1 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 1 ) )
VAR p2 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 2 ) )
VAR p3 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 3 ) )
VAR p4 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 4 ) )
VAR p5 =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () - 5 ) )
RETURN
SWITCH (
TRUE (),
currentcount < 10, UNICHAR ( 128994 ),
currentcount >= 10,
IF (
p1 < 10
|| p2 < 10
|| p3 < 10
|| p4 < 10
|| p5 < 10,
UNICHAR ( 128992 ),
IF (
p1 >= 10
&& p2 >= 10
&& p3 >= 10
&& p4 >= 10
&& p5 >= 10,
UNICHAR ( 128308 )
)
)
)
in my sample it should be all green
Attached the sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jitpbi
Can you share some sample data as a file or at least copy.paste it into the forum/ Even with my high res screen I can't make out the column names or data values with my bad eyesight.
Regards
Phil
Proud to be a Super User!
User | Count |
---|---|
75 | |
75 | |
45 | |
31 | |
27 |
User | Count |
---|---|
99 | |
89 | |
52 | |
48 | |
46 |