Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |