Forum Discussion

jitpbi's avatar
jitpbi
Post Patron
5 years ago
Solved

additional condition in the dax code

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-counts/m-p/1620892#M653488

 

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:

 

ICRInverterSCBUnitCount of AnomalyActualDateTime
ICR1INV1SCB10U113001/17/2021 7:17
ICR1INV1SCB10U112341/17/2021 7:27
ICR1INV1SCB10U113251/17/2021 8:19
ICR1INV1SCB10U114321/17/2021 8:24
ICR1INV1SCB10U115621/17/2021 9:21
ICR1INV1SCB10U1101/17/2021 9:31
ICR1INV1SCB10U113251/17/2021 9:42
ICR1INV1SCB10U114321/17/2021 10:15
ICR1INV1SCB10U115621/17/2021 11:36
ICR1INV1SCB10U113001/17/2021 15:18
ICR1INV1SCB10U112341/17/2021 16:20
ICR1INV1SCB10U113251/17/2021 17:17
ICR1INV1SCB10U114321/17/2021 17:27
ICR1INV1SCB10U115621/17/2021 18:14
ICR2INV1SCB10U1101/17/2021 7:17
ICR2INV1SCB10U113001/17/2021 7:27
ICR2INV1SCB10U112341/17/2021 8:19
ICR2INV1SCB10U113251/17/2021 8:24
ICR2INV1SCB10U114321/17/2021 9:21
ICR2INV1SCB10U1101/17/2021 9:31
ICR2INV1SCB10U1101/17/2021 9:42
ICR2INV1SCB10U113001/17/2021 10:15
ICR2INV1SCB10U112341/17/2021 11:36
ICR2INV1SCB10U113251/17/2021 14:16
ICR2INV1SCB10U114321/17/2021 14:21
ICR2INV1SCB10U1101/17/2021 14:36
ICR2INV1SCB10U114301/17/2021 15:02
ICR2INV1SCB10U1101/17/2021 15:18
ICR2INV1SCB10U1101/17/2021 17:17
ICR2INV1SCB10U114321/17/2021 17:27
ICR2INV1SCB10U113211/17/2021 18:14
ICR1INV1SCB10U2101/17/2021 18:14
ICR2INV1SCB10U2101/17/2021 18:14

 

Please suggest how to add this additional condition in the dax.

 

Thanks

Need Help
  • Icey's avatar
    Icey
    5 years ago

    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.

5 Replies

  • jitpbi's avatar
    jitpbi
    Post Patron
    5 years ago

    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's avatar
      Icey
      Community Support
      5 years ago

      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's avatar
        jitpbi
        Post Patron
        5 years ago

        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