Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
In one of my recent question i missed one condition addition to detail mentioned in this post.
The below is the code which was running successfully and accepted as solution in the this post:
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 )
)
)
)
Additonal condition: If the field "Actual" has value 0 then it should display with a grey colour.
The below is the sample data:
ICR | Inverter | SCB | Unit | Count of Anomaly | Actual | DateTime |
ICR1 | INV1 | SCB10 | U1 | 1 | 300 | 1/17/2021 7:17 |
ICR1 | INV1 | SCB10 | U1 | 1 | 234 | 1/17/2021 7:27 |
ICR1 | INV1 | SCB10 | U1 | 1 | 325 | 1/17/2021 8:19 |
ICR1 | INV1 | SCB10 | U1 | 1 | 432 | 1/17/2021 8:24 |
ICR1 | INV1 | SCB10 | U1 | 1 | 562 | 1/17/2021 9:21 |
ICR1 | INV1 | SCB10 | U1 | 1 | 0 | 1/17/2021 9:31 |
ICR1 | INV1 | SCB10 | U1 | 1 | 325 | 1/17/2021 9:42 |
ICR1 | INV1 | SCB10 | U1 | 1 | 432 | 1/17/2021 10:15 |
ICR1 | INV1 | SCB10 | U1 | 1 | 562 | 1/17/2021 11:36 |
ICR1 | INV1 | SCB10 | U1 | 1 | 300 | 1/17/2021 15:18 |
ICR1 | INV1 | SCB10 | U1 | 1 | 234 | 1/17/2021 16:20 |
ICR1 | INV1 | SCB10 | U1 | 1 | 325 | 1/17/2021 17:17 |
ICR1 | INV1 | SCB10 | U1 | 1 | 432 | 1/17/2021 17:27 |
ICR1 | INV1 | SCB10 | U1 | 1 | 562 | 1/17/2021 18:14 |
ICR2 | INV1 | SCB10 | U1 | 1 | 0 | 1/17/2021 7:17 |
ICR2 | INV1 | SCB10 | U1 | 1 | 300 | 1/17/2021 7:27 |
ICR2 | INV1 | SCB10 | U1 | 1 | 234 | 1/17/2021 8:19 |
ICR2 | INV1 | SCB10 | U1 | 1 | 325 | 1/17/2021 8:24 |
ICR2 | INV1 | SCB10 | U1 | 1 | 432 | 1/17/2021 9:21 |
ICR2 | INV1 | SCB10 | U1 | 1 | 0 | 1/17/2021 9:31 |
ICR2 | INV1 | SCB10 | U1 | 1 | 0 | 1/17/2021 9:42 |
ICR2 | INV1 | SCB10 | U1 | 1 | 300 | 1/17/2021 10:15 |
ICR2 | INV1 | SCB10 | U1 | 1 | 234 | 1/17/2021 11:36 |
ICR2 | INV1 | SCB10 | U1 | 1 | 325 | 1/17/2021 14:16 |
ICR2 | INV1 | SCB10 | U1 | 1 | 432 | 1/17/2021 14:21 |
ICR2 | INV1 | SCB10 | U1 | 1 | 0 | 1/17/2021 14:36 |
ICR2 | INV1 | SCB10 | U1 | 1 | 430 | 1/17/2021 15:02 |
ICR2 | INV1 | SCB10 | U1 | 1 | 0 | 1/17/2021 15:18 |
ICR2 | INV1 | SCB10 | U1 | 1 | 0 | 1/17/2021 17:17 |
ICR2 | INV1 | SCB10 | U1 | 1 | 432 | 1/17/2021 17:27 |
ICR2 | INV1 | SCB10 | U1 | 1 | 321 | 1/17/2021 18:14 |
ICR1 | INV1 | SCB10 | U2 | 1 | 0 | 1/17/2021 18:14 |
ICR2 | INV1 | SCB10 | U2 | 1 | 0 | 1/17/2021 18:14 |
Please suggest how to add this additional condition in the dax.
Thanks
Solved! Go to Solution.
Hi @jitpbi ,
Sorry to reply late. Try to change the expression like so:
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 )
&& 'Table'[Count of Anomaly] = 1 ----------added
)
Or you can add "[Count of Anomaly] = 1" at each var count like so:
VAR currentcount =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () ) && [Count of Anomaly] = 1 )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @amitchandak ,
The above code is working fine for the detailed requirement. I only need to add one more condition to this code.
Adding conditional formatting doesnt work for my entire requirement.
Thanks
Hi @jitpbi ,
Additonal condition: If the field "Actual" has value 0 then it should display with a grey colour.
If it means that in each cell of the Matrix visual, at least one of all the 'Actual' values is 0. Or in other words, the minimum value is 0. Then add another condition like this:
count =
VAR tab =
xxxxx
VAR currentcount =
xxxxx
VAR p1 =
xxxxx
VAR p2 =
xxxxxx
VAR p3 =
xxxxxx
VAR p4 =
xxxxxx
VAR p5 =
xxxxxx
RETURN
IF (
MIN ( 'Table'[Actual] ) = 0,
UNICHAR ( 9898 ),
SWITCH (
xxxxxxx
)
)
If it means that in each cell of the Matrix visual, the sum value is 0. Then add another condition like this:
count =
VAR tab =
xxxxx
VAR currentcount =
xxxxx
VAR p1 =
xxxxx
VAR p2 =
xxxxxx
VAR p3 =
xxxxxx
VAR p4 =
xxxxxx
VAR p5 =
xxxxxx
RETURN
IF (
SUM ( 'Table'[Actual] ) = 0,
UNICHAR ( 9898 ),
SWITCH (
xxxxxxx
)
)
Please let me know if I understand incorrectly.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey ,
Yes, it required when Sum value is 0 and your shared logic works perfectly.
Also, I have one doubt on the initial code as it counts all the rows:
VAR currentcount =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () ) )
In the dataset field "count of anomaly" has 1 and 0 values (my shared data sample above has all 1 as i have taken just a small sample). so by considering 1 and 0 in this field, It should count only those records where field "count of anomaly" is having 1 only and ignore the rows which have 0 value.
Please suggest what change required in the code.
Thanks
Hi @jitpbi ,
Sorry to reply late. Try to change the expression like so:
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 )
&& 'Table'[Count of Anomaly] = 1 ----------added
)
Or you can add "[Count of Anomaly] = 1" at each var count like so:
VAR currentcount =
COUNTROWS ( FILTER ( tab, DATE(YEAR([DateTime]),MONTH([DateTime]),DAY([DateTime])) = TODAY () ) && [Count of Anomaly] = 1 )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@jitpbi , I think above one will disply a unichar in each row.
You can do conditional formatting using color measure with "field Value" option
Color = if([Actual] > 0, "grey","white")
refer for steps
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |