Forum Discussion
Conditional formatting not applied
Dear All,
created matrix visual from sql data which contain more than 20 column. i want to conditional fomatting on column name based. please refer my below code and screen shot. as per condition given but output is comes only in red color. please help me to short it out.
Thanking you,
Dhrupal shah
Production_Color =
SWITCH(
TRUE(),
TRIM('Production'[ComponentName]) = "CTL 1" && 'Production'[QtyRecv] > 50 , "#008000", // Green
TRIM('Production'[ComponentName]) = "CTL 1" && 'Production'[QtyRecv] >=35 && 'Production'[QtyRecv] <= 50, "#000a00", // Black
TRIM('Production'[ComponentName]) = "CTL 1" && 'Production'[QtyRecv] > 0 && 'Production'[QtyRecv] < 35, "#FF0000" //Red
)
Hi dhrupal_shah ,
You can try the following measure.
Measure = VAR _sum = SUM('Production'[QtyRecv]) RETURN SWITCH( TRUE(), TRIM(MAX('Production'[ComponentName])) = "CTL 1" && _sum > 50 , "#008000", // Green TRIM(MAX('Production'[ComponentName])) = "CTL 1" && _sum >=35 && _sum <= 50, "#000a00", // Black TRIM(MAX('Production'[ComponentName])) = "CTL 1" && _sum > 0 && _sum < 35, "#FF0000" //Red )Also, depending on whether you want all columns to have conditional formatting added to them? If so, just remove the condition “TRIM(MAX(‘Production’[ComponentName])) = ‘CTL 1’”
More detailed information can be found in the attachment.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Hi dhrupal_shah
Download PBIX file with the example below
You don't need to name the column in the measure, you apply the measure to the column as conditional formatting using this DAX
Production_Color = VAR _QtyRecv = SELECTEDVALUE(Production[QtyRecv]) RETURN SWITCH( TRUE(), _QtyRecv > 50 , "#008000", // Green _QtyRecv >=35 && _QtyRecv <= 50, "#000a00", // Black _QtyRecv > 0 && _QtyRecv < 35, "#FF0000" //Red )Regards
Phil
Hi dhrupal_shah ,
Based on your description, your Production_Color should be a calculated column and when it is put into a conditional format, the aggregation method can only be First or Last.
Whereas your QtyRecv field can have multiple aggregation methods in the matrix.
Therefore, you need to write the calculated column with the same aggregation method as in the matrix. I wrote this using sum as an example:
Production_Color = VAR _sum = CALCULATE(SUM(Production[QtyRecv]),FILTER('Production','Production'[DocumentDate] = EARLIER(Production[DocumentDate]) && 'Production'[ComponentName] = EARLIER(Production[ComponentName]))) RETURN SWITCH( TRUE(), TRIM('Production'[ComponentName]) = "CTL 1" && _sum > 50 , "#008000", // Green TRIM('Production'[ComponentName]) = "CTL 1" && _sum >=35 && _sum <= 50, "#000a00", // Black TRIM('Production'[ComponentName]) = "CTL 1" && _sum > 0 && _sum < 35, "#FF0000" //Red )If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for your solution.
not get result as expected. even 48.17 it show green.
Hi dhrupal_shah ,
Can you share sample data in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi dhrupal_shah ,
You can try the following expression.
Column = VAR _sum = CALCULATE(SUM(Production[QtyRecv]),FILTER('Production','Production'[DocumentDate] = EARLIER(Production[DocumentDate]) && TRIM('Production'[ComponentName]) = "CTL 1")) RETURN SWITCH( TRUE(), TRIM('Production'[ComponentName]) = "CTL 1" && _sum > 50 , "#008000", // Green TRIM('Production'[ComponentName]) = "CTL 1" && _sum >=35 && _sum <= 50, "#000a00", // Black TRIM('Production'[ComponentName]) = "CTL 1" && _sum > 0 && _sum < 35, "#FF0000" //Red )If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Clara,
this is not workable you can see in below screenshot, also it is hardcore for CTL 1. we achive it with adding one new column and it work fine.
thanks for your support.
Hi dhrupal_shah ,
You can try the following measure.
Measure = VAR _sum = SUM('Production'[QtyRecv]) RETURN SWITCH( TRUE(), TRIM(MAX('Production'[ComponentName])) = "CTL 1" && _sum > 50 , "#008000", // Green TRIM(MAX('Production'[ComponentName])) = "CTL 1" && _sum >=35 && _sum <= 50, "#000a00", // Black TRIM(MAX('Production'[ComponentName])) = "CTL 1" && _sum > 0 && _sum < 35, "#FF0000" //Red )Also, depending on whether you want all columns to have conditional formatting added to them? If so, just remove the condition “TRIM(MAX(‘Production’[ComponentName])) = ‘CTL 1’”
More detailed information can be found in the attachment.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
