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...
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.

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

     

    • Icey's avatar
      Icey
      Community Support
      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.