topic Re: Cell should mark itself if difference is higher than 0,5 to previous cell in DAX Commands and Tips

Cell should mark itself if difference is higher than 0,5 to previous cell

Anonymous — Fri, 24 May 2024 12:39:49 GMT

Hi dear community,

I have a column in which my values are listed. Nothing special, just like this:

1
2
2.3
1.9
1.3

What I want to accomplish is, that the cells will e.g. turn red if the difference between them is higher than 0.5, like this:

1
2██████
2.3
1.9
1.3█████
1.1
1.7█████
1.3

Please excuse the poor depiction. If it's easier, it can also be a marking in a cell in the next colum.

Thank you very much for your time!

Kind Regards

Re: Cell should mark itself if difference is higher than 0,5 to previous cell

DataNinja777 — Fri, 24 May 2024 14:39:05 GMT

Hi Anonymous ,

You can achieve your required output in many different ways, but since your numerical column has numbers which are repetitive and not in numerical order, and the data table in Power BI does not store the order as it is in your column I've added an index column using Power Query to make things easier in terms of respecting your original data order without aggregation.

Then, I've added a column next to your original column to indicate the previous row value.

Then, I've added a simple calculated column to indicate the difference with the previous column larger than 0.5 in absolute value as indicated in your example.

Lastly, I've written a color measure to be used in the background color for orignal column.

I attach an example pbix file.

Best regards,

Re: Cell should mark itself if difference is higher than 0,5 to previous cell

ThxAlot — Fri, 24 May 2024 19:11:14 GMT

What you need are only 2 simple things:

Index column for keep the orders of your original dataset;
a DAX measure for background color

Re: Cell should mark itself if difference is higher than 0,5 to previous cell

DataNinja777 — Sat, 25 May 2024 00:20:26 GMT

Hi ThxAlot

Good idea 😀 How did you manage to hide index column from your final output as shown below?

Best regards,

Re: Cell should mark itself if difference is higher than 0,5 to previous cell

ThxAlot — Sat, 25 May 2024 04:27:07 GMT

Hello, DataNinja777 , you can toggle off "Text wrap" for both "Values" and "Column headers"; then manually drag "Index" width to hide it.