Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Solved! Go to Solution.
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.
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.
Hi Yolo
This is brilliant, works a treat. Thanks so much for your help.
Kind regards
Karen
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |