Conditional Formating Issues With Measure and text

[KarenFingerhut]

Hi There

I'm wanting to create 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

I have a DAX statement with the following for Current year

# Metric CYTD =

    IF(Metrics_Join[Choice Metric] = 1,

        ROUND(CALCULATE(Metrics[# Value Numerator CYTD]),0),

  IF(Metrics_Join[Choice Metric] = 2,

      ROUND(CALCULATE(Metrics[# Value Numerator CYTD]),0),

    IF(Metrics_Join[Choice Metric] = 3,

      ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator CYTD], Metrics[# Value Denominator CYTD])),1),

    IF(Metrics_Join[Choice Metric] = 4,

         ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator CYTD], Metrics[# Value Denominator CYTD])),1),

    IF(Metrics_Join[Choice Metric] = 5,

        ROUND(CALCULATE(Metrics[# Value Numerator CYTD]),2),

    IF(Metrics_Join[Choice Metric] = 6,

        CALCULATE(DIVIDE(Metrics[# Value Numerator CYTD], Metrics[# Value Denominator CYTD]))*100,

    IF(Metrics_Join[Choice Metric] = 7,

      ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator CYTD], Metrics[# Value Denominator CYTD])),1),

      IF(Metrics_Join[Choice Metric] = 8,

        ROUND(CALCULATE(Metrics[# Value Numerator CYTD]),0),

      BLANK()))))))))

And the below is for previous year

# Metric PYTD =

    IF(Metrics_Join[Choice Metric] = 1,

        ROUND(CALCULATE(Metrics[# Value Numerator LYTD]),0),

  IF(Metrics_Join[Choice Metric] = 2,

      ROUND(CALCULATE(Metrics[# Value Numerator LYTD]),0),

    IF(Metrics_Join[Choice Metric] = 3,

      ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator LYTD], Metrics[# Value Denominator LYTD])),1),

    IF(Metrics_Join[Choice Metric] = 4,

         ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator LYTD], Metrics[# Value Denominator LYTD])),1),

    IF(Metrics_Join[Choice Metric] = 5,

        ROUND(CALCULATE(Metrics[# Value Numerator LYTD]),2),

    IF(Metrics_Join[Choice Metric] = 6,

        CALCULATE(DIVIDE(Metrics[# Value Numerator LYTD], Metrics[# Value Denominator LYTD]))*100,

    IF(Metrics_Join[Choice Metric] = 7,

      ROUND(CALCULATE(DIVIDE(Metrics[# Value Numerator LYTD], Metrics[# Value Denominator LYTD])),1),

      IF(Metrics_Join[Choice Metric] = 8,

        ROUND(CALCULATE(Metrics[# Value Numerator LYTD]),0),

      BLANK()))))))))

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 =

                        IF([Metric Sort Order] = 1 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),"Up Red",

                        IF([Metric Sort Order] = 1 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),"Down Green",

                        IF([Metric Sort Order] = 1 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),"Same Amber",

                        IF([Metric Sort Order] = 2 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),"Up Green",

                        IF([Metric Sort Order] = 2 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),"Down Red",

                        IF([Metric Sort Order] = 2 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),"Same Amber",

                        IF([Metric Sort Order] = 3 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),"Down Green",

                        IF([Metric Sort Order] = 3 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),"Up Red",

                        IF([Metric Sort Order] = 3 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),"Same Amber",

                        IF([Metric Sort Order] = 4 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),"Up Red",

                        IF([Metric Sort Order] = 4 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),"Down Green",

                        IF([Metric Sort Order] = 4 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),"Same Amber",

                        IF([Metric Sort Order] = 5 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),"Up Red",

                        IF([Metric Sort Order] = 5 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),"Down Green",

                        IF([Metric Sort Order] = 5 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),"Same Amber",

                        IF([Metric Sort Order] = 6 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),"Up Green",

                        IF([Metric Sort Order] = 6 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),"Down Red",

                        IF([Metric Sort Order] = 6 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),"Same Amber",

                        IF([Metric Sort Order] = 7 && CALCULATE([# Metric PYTD] > [# Metric CYTD]) ,"Down Green",

                        IF([Metric Sort Order] = 7 && CALCULATE([# Metric PYTD] < [# Metric CYTD]) ,"Up Red",

                        IF([Metric Sort Order] = 7 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),"Same Amber",

                        IF([Metric Sort Order] = 8 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),"Up Green",

                        IF([Metric Sort Order] = 8 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),"Down Red",

                        IF([Metric Sort Order] = 8 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),"Same Amber"

                        , BLANK()))))))))))))))))))))))))

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

But as soon as I add the conditional formatting DAX column in it does the below, duplicates the rows and splits out the values

and  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

Can one of you kind gurus 🙂 please help as I expect its something in my DAX statement

Many thanks

Karen

[Anonymous · Accepted solution]

Hi @KarenFingerhut 

You can refer to the following code

^ YoY Conditional Formatting 4 =
                        IF([Metric Sort Order] = 1 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),1,
                        IF([Metric Sort Order] = 1 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),2,
                        IF([Metric Sort Order] = 1 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),3,

                        IF([Metric Sort Order] = 2 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),4,
                        IF([Metric Sort Order] = 2 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),5,
                        IF([Metric Sort Order] = 2 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),3,

                        IF([Metric Sort Order] = 3 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),2,
                        IF([Metric Sort Order] = 3 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),1,
                        IF([Metric Sort Order] = 3 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),3,

                        IF([Metric Sort Order] = 4 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),1,
                        IF([Metric Sort Order] = 4 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),2,
                        IF([Metric Sort Order] = 4 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),3,

                        IF([Metric Sort Order] = 5 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),1,
                        IF([Metric Sort Order] = 5 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),2,
                        IF([Metric Sort Order] = 5 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),3,

                        IF([Metric Sort Order] = 6 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),4,
                        IF([Metric Sort Order] = 6 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),5,
                        IF([Metric Sort Order] = 6 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),3,

                        IF([Metric Sort Order] = 7 && CALCULATE([# Metric PYTD] > [# Metric CYTD]) ,2,
                        IF([Metric Sort Order] = 7 && CALCULATE([# Metric PYTD] < [# Metric CYTD]) ,1,
                        IF([Metric Sort Order] = 7 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),3,

                        IF([Metric Sort Order] = 8 && CALCULATE([# Metric CYTD] > [# Metric PYTD]),4,
                        IF([Metric Sort Order] = 8 && CALCULATE([# Metric CYTD] < [# Metric PYTD]),5,
                        IF([Metric Sort Order] = 8 && CALCULATE([# Metric CYTD] = [# Metric PYTD]),3
))))))))))))))))))))))))

Then put it to the conditional formatting 

Output

So that the fields will not be duplicated.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KarenFingerhut]

Hi Yolo

This is brilliant, works a treat. Thanks so much for your help.

Kind regards

Karen