Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Conditional Formating Issues With Measure and text

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
KarenFingerhut_0-1678099427352.png

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

 

KarenFingerhut_2-1678099905696.png

 

KarenFingerhut_1-1678099678321.png
Can one of you kind gurus 🙂 please help as I expect its something in my DAX statement
 
Many thanks
Karen
 
 
 
1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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 

vxinruzhumsft_0-1678256051780.png

vxinruzhumsft_1-1678256237828.png

Output

vxinruzhumsft_2-1678256331883.png

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.

 

 

View solution in original post

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

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 

vxinruzhumsft_0-1678256051780.png

vxinruzhumsft_1-1678256237828.png

Output

vxinruzhumsft_2-1678256331883.png

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.

 

 

Hi Yolo

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

Kind regards

Karen

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.