additional condition in the dax code

[jitpbi]

Hi,

In one of my recent question i missed one condition addition to detail mentioned in this post.

https://community.powerbi.com/t5/Desktop/show-different-colours-on-matrix-visual-based-on-fault-coun...

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

[jitpbi]

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

[Icey]

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.

[jitpbi]

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

[Icey · Accepted 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.

[amitchandak]

@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