Forum Discussion
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:
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
- AnonymousNot 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