Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Objective - highlight the differences between products on a row by row basis
So, in this simple example, I select a few 'recipes' from a slicer, which adds a few columns to the matrix. On each row (ie for each type) I want to colour them based on their description, so use a different colour when there is a difference. This is to help the user compare a small number of recipes and easily spot and check the differences.
I am currently doing this example with conditional formatting. My very basic approach here is using a calculated column based on the length of the description - it sort of creates the effect i am aiming for, but obviously can't show a difference between two items with the same description length (apple and peach are both the same colour).
I wondered about building some more complex calculation based on each unichar code in the string, or a checksum or hash of the string, but looking for examples that doesn't seem as straightforward as i'd hoped, and may be overkill.
Ideally i'd like to really draw attention to the different cells, as some text fields in my dataset are pretty similar and could be easily missed - so, while this example is a gradient filter I think a better approach could be one where an integer is assigned and incremented each time a difference is encountered - sort of like a running total, but modified by the slicer/filter so we are always dealing with 1s,2s,3s etc. This would be simple to set up a conditional format so adjacent values are really obvious.
(I got stuck with this, as running total examples I found are generally calculated columns based on date field) - but this would mean the above table is coloured with a field returning mostly 1s, just with pommegrante = 2 and peach = 3 and then onion = 2 (as each row encountered differences)
I'm sure there are several ways to approach this seemingly simple problem - i'd really apprecaite some guidance what direction to take!
Sample data:
Recipe_ID | Type | Description |
1 | Fruit | Apple |
1 | Vegetable | Carrot |
2 | Fruit | Pomegranate |
2 | Vegetable | Carrot |
3 | Fruit | Apple |
3 | Vegetable | Onion |
4 | Fruit | Apple |
4 | Vegetable | Onion |
5 | Fruit | Peach |
5 | Vegetable | Carrot |
Hi @jpc ,
Below is my table:
The following Dax might work for you:
Column =
var _app5 = LEN('Table'[Description])
RETURN
SWITCH(
TRUE(),
_app5 <= 5 , 1 ,
_app5 <10 && _app5 > 5 , 2,
_app5 < 15 && _app5 >10 , 3
)
The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for taking the time to read my question, and build a solution.
Unfortunately, i still see in your example that Apple and Peach are still the same colour. Length of the string is not an effective method, i am looking to improve on this with some string comparison, if you have any ideas on this?
User | Count |
---|---|
84 | |
79 | |
69 | |
46 | |
43 |
User | Count |
---|---|
106 | |
50 | |
49 | |
40 | |
39 |