Forum Discussion

jitpbi's avatar
jitpbi
Icon for Post Patron rankPost Patron
5 years ago

correction in the dax

Hi,

 

In one of my recent question i got the solution from community support. I need to show different colour on matrix visual based on few conditions. the below link is for the original post where i posted my question previously:

 

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

 

The below is the sample dataset:

 

 

The below are the conditions to display the colours:

 

1.Green Colour:  if the anomaly count is less than 10 for the current day

 

2. Orange Colour: if the anomaly count is equal or greater than 10 for the current day and the anomaly count for the any day in the last 6 days is less than 10 

 

3. Red Colour: if the anomaly count is equal or greater than 10 for the current day and anomaly count for each day in the last 6 days is equal or greater than 10 

 

4. If the field "Actual" has value 0 then it should display with a grey colour.

 

5. we have to conisder the values only for the time in between only 9:30 AM to 5:30 PM 

 

 

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

      IF (

               MIN ( 'Table'[Actual] )  = 0,

               UNICHAR ( 9898 ),

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

 

I am not able to test the result successfully as i have doubt on the field used in the above code to count the rows is [DateTime]. The count should be for those rows where field Anomaly has value "1" only. it should not count the rows which has value "0".

 

I have highlighted the code which need attention with red colour above.

Please suggest what change need to be done in this code to achieve the desired result.

 

Thanks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi jitpbi ,

     

    Could you please share your sample files and the expected result to have a clear understanding of your question? I can do some tests for you.

    You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

     

    Best Regards,

    Yuna