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