topic Re: Conditional Formatting in DAX Commands and Tips

Conditional Formatting

e175429 — Tue, 30 Jul 2024 16:25:57 GMT

Hello All,

This is a part of my curent table:

For the "Score Trend", instead of it saying Decline, No change, Improvemement, I would like it to instead show a arrow going up

(for improvement), down (for decline), and a dash ➖ (for no improvement) and then I don't want the words to show either.

Re: Conditional Formatting

Anonymous — Wed, 31 Jul 2024 01:42:23 GMT

Hi e175429 ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new measure to calculate score change.

Score change = var _last = SUMX('Table', 'Table'[Last Score]) var _current = SUMX('Table', 'Table'[Current Score]) RETURN DIVIDE(_current - _last, 100)

3.Create the new measure to filter the score trend.

Score trend = IF([Score change] > 0, "Improvement", IF([Score change] < 0 , "Decline", "N/A") )

4.Select the Score trend > conditional formatting > Icons.

5.Select Rules > Score trend > Icons only. Set the Rules.

6.The result is shown below.

Best Regards,

Wisdom Wu

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

Re: Conditional Formatting

Ray_Minds — Wed, 31 Jul 2024 09:44:20 GMT

Hi e175429
1. First Create data in Power BI using the enter data option in the home tab

2. After that create the calculated column and get the Difference in Sales

3. After that create the sale changes

4. Select the Sale trend > conditional formatting > Icons.

5. Select Rules > Score trend > Icons only. Set the Rules.

6. The result is shown below.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Re: Conditional Formatting

e175429 — Wed, 31 Jul 2024 21:19:35 GMT

Hello!

I appreciate your response.

I am getting the following error when calculating the score change:

And I can't calculate the score trend until I score change.

The reason I am getting this error is because there are some people with no scores in the Last Score column. Currently, for those people, it says "N/A". They don't have a "Last Score" but they do have a "Current Score".

I purposely did not write 0 in their "'Last Score" column because I don't want that to be confused with their current score and having "No Improvement". It's not that they didn't improve, they just were not involved the first go around.

Does that make sense? So, as the error says, SUMX cannot work on values with "N/A" in it. And I understand that. But what should I put in place of "N/A"?

Re: Conditional Formatting

Anonymous — Fri, 02 Aug 2024 08:28:06 GMT

Hi e175429 ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new column to filter the N/A.

filterlastscore = IF('Table 2'[Last Score] = "N/A", BLANK(), 'Table 2'[Last Score])

3.Create the new column to calculate the score change. Change the column format to percentage.

Score change = DIVIDE('Table 2'[Current Score] - 'Table 2'[filterlastscore], 100)

4.Create the new measure to calculate score trend.

Scoretren = IF([Score change] > 0, "Improvement", IF([Score change] < 0 , "Decline", "N/A") )

Best Regards,

Wisdom Wu

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

Re: Conditional Formatting

e175429 — Fri, 02 Aug 2024 14:56:45 GMT

Hi Anonymous,

Is there a way to factor "No trend" in to the "Trend" column (due to them not participating), because technically they didn't improve(or decline)? For the ones who originally had no score, they shouldn't have a trend.

Re: Conditional Formatting

e175429 — Fri, 02 Aug 2024 19:46:53 GMT

Hey Anonymous,

I figured it out. I had to combine a IF AND statement.

Results:

Thank you sooooo much for your help. I would have been stuck forever.