Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
55 | |
55 | |
36 | |
30 |
User | Count |
---|---|
79 | |
66 | |
45 | |
44 | |
42 |