Agenda:
1. Expectation
2. Problem Statement
3. Solution
- Expectation
Here we have data for all products, and we want to see the sales of each product, but we also want to highlight the products without filtering them.
The visuals must look like this:
2 . Problem Statement
As we know, we have two options in terms of filtering.
- Normal Filter: If you select one product in Slicer, only that customer will be visible, while the others will be hidden.
2. Edit Interaction: If you disable the interaction between the table and the slicer, the slicer will not work at all on the table.
However, in the preceding expectation, we simply want to highlight selected products without filtering.
3. Solution
The following are the steps to achieving the desired visual:
Create one summarised product table.
Summarized Product table =
SUMMARIZE(
Products,
Products[ProductKey], Products[ProductName]
)
Do not connect this table using relationships.
Data modelling should look like this:
Add the product name column in the slicer from the summarised product table.
We must now create a new measure to establish a virtual relationship between the summarised product table and the product table.
In this Measure, we are just calculating [Sales Amount] and created a virtual relationship only for this measure by using the “Treatas” Function
And add this measure in the table Visual
If we only select one product, only that product’s value should be visible, and all other values should be blank.
Now we need to write one more measure for highlighting.
In this measure, we are validating two conditions:
If the product name slicer is selected, and [Sales Amount] must be exactly equal to [Product Relationship], then “D9B300” (color code) is used.
Now select Table Visual, and in the Columns Section, add Conditional Formatting for Columns: Product Name and Sales Amount.
Let’s add conditional formatting for the product name:
As shown in the image above, in the columns section, click on the down arrow then click on Conditional Formatting à Background color
This window will appear.
In the Format style, select Field Value.
Choose Measure, which was created for the highlighted row, in this case, Highlight Product Measure ->click OK
Add conditional formatting for the sales amount as shown above.
Remove the “Product Relationship” column from the table.
Now select any value in Slicer and it will work as expected.
Conclusion: We can just highlight columns by using slicers
Rubika Jadhav
Data Analyst
Addend Analytics