Dynamic Conditional Formatting With Measures And Test and Arrow

Question

Conditional Formating Issues With Measure and text4 hours agoHi ThereI'm wanting to create some dynamic conditional formatting based on Text or number if possible but to enable me to have Green Arrow Up / Green Arrow Downs / Red Arrow Up / Red Arrow Down / Amber horizontal arrow&nbsp;I have a DAX statement with the following for Current year&nbsp;# Metric CYTD =&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;1,&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(Metrics[# Value Numerator CYTD]),0),&nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;2,&nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(Metrics[# Value Numerator CYTD]),0),&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;3,&nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator CYTD],&nbsp;Metrics[# Value Denominator CYTD])),1),&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;4,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator CYTD],&nbsp;Metrics[# Value Denominator CYTD])),1),&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;5,&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(Metrics[# Value Numerator CYTD]),2),&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;6,&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CALCULATE(DIVIDE(Metrics[# Value Numerator CYTD],&nbsp;Metrics[# Value Denominator CYTD]))*100,&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;7,&nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator CYTD],&nbsp;Metrics[# Value Denominator CYTD])),1),&nbsp; &nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;8,&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(Metrics[# Value Numerator CYTD]),0),&nbsp; &nbsp; &nbsp;&nbsp;BLANK()))))))))&nbsp;And the below is for previous year# Metric PYTD =&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;1,&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(Metrics[# Value Numerator LYTD]),0),&nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;2,&nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(Metrics[# Value Numerator LYTD]),0),&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;3,&nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator LYTD],&nbsp;Metrics[# Value Denominator LYTD])),1),&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;4,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator LYTD],&nbsp;Metrics[# Value Denominator LYTD])),1),&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;5,&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(Metrics[# Value Numerator LYTD]),2),&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;6,&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CALCULATE(DIVIDE(Metrics[# Value Numerator LYTD],&nbsp;Metrics[# Value Denominator LYTD]))*100,&nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;7,&nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator LYTD],&nbsp;Metrics[# Value Denominator LYTD])),1),&nbsp; &nbsp; &nbsp;&nbsp;IF(Metrics_Join[Choice Metric]&nbsp;=&nbsp;8,&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ROUND(CALCULATE(Metrics[# Value Numerator LYTD]),0),&nbsp; &nbsp; &nbsp;&nbsp;BLANK()))))))))&nbsp;I'm the creating some dax to compare CY to PY so I can do some conditional formatting based on arrows up or down^ YoY Conditional Formatting 4 =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;1&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&gt;&nbsp;[# Metric PYTD]),"Up Red",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;1&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&lt;&nbsp;[# Metric PYTD]),"Down Green",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;1&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;=&nbsp;[# Metric PYTD]),"Same Amber",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;2&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&gt;&nbsp;[# Metric PYTD]),"Up Green",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;2&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&lt;&nbsp;[# Metric PYTD]),"Down Red",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;2&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;=&nbsp;[# Metric PYTD]),"Same Amber",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;3&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&lt;&nbsp;[# Metric PYTD]),"Down Green",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;3&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&gt;&nbsp;[# Metric PYTD]),"Up Red",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;3&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;=&nbsp;[# Metric PYTD]),"Same Amber",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;4&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&gt;&nbsp;[# Metric PYTD]),"Up Red",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;4&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&lt;&nbsp;[# Metric PYTD]),"Down Green",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;4&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;=&nbsp;[# Metric PYTD]),"Same Amber",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;5&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&gt;&nbsp;[# Metric PYTD]),"Up Red",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;5&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&lt;&nbsp;[# Metric PYTD]),"Down Green",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;5&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;=&nbsp;[# Metric PYTD]),"Same Amber",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;6&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&gt;&nbsp;[# Metric PYTD]),"Up Green",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;6&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&lt;&nbsp;[# Metric PYTD]),"Down Red",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;6&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;=&nbsp;[# Metric PYTD]),"Same Amber",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;7&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric PYTD]&nbsp;&gt;&nbsp;[# Metric CYTD]) ,"Down Green",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;7&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric PYTD]&nbsp;&lt;&nbsp;[# Metric CYTD]) ,"Up Red",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;7&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;=&nbsp;[# Metric PYTD]),"Same Amber",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;8&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&gt;&nbsp;[# Metric PYTD]),"Up Green",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;8&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;&lt;&nbsp;[# Metric PYTD]),"Down Red",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;IF([Metric Sort Order]&nbsp;=&nbsp;8&nbsp;&amp;&amp;&nbsp;CALCULATE([# Metric CYTD]&nbsp;=&nbsp;[# Metric PYTD]),"Same Amber"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,&nbsp;BLANK()))))))))))))))))))))))))&nbsp;The issue I am having is that the below without me adding the above formula column in shows it like this . Everything on the same row&nbsp;But as soon as I add the conditional formatting DAX column in it does the below, duplicates the rows and splits out the valuesand&nbsp; the conditions on to separate rows, therefore I cannot get the conditional formatting to work as it will either only chose the First or Last&nbsp;&nbsp;&nbsp;&nbsp;Can one of you kind gurus&nbsp;&nbsp;please help as I expect its something in my DAX statement&nbsp;Many thanksKaren&nbsp;&nbsp;

karenfingerhut · Answer

Just following on from Above, I do have additional slicers that I need this conditional format to be dynamic with as well.Thank you

Forum Discussion

Dynamic Conditional Formatting With Measures And Test and Arrow

1 Reply

Related content

Conditional Formatting using DAX

Conditional Formatting

Conditional Formatting

Matrix Row Conditional Formatting

Conditional Formatting