Power BI is one of the best BI tools available in the market. If you have used it, you will realise that it is a user-friendly tool which offers drag and drop features and has self service capabilities built into it.
We can filter down the data according to the selected category or item using the slicer visual or selecting other visuals. In Power BI, there is an ‘Edit Interaction feature’ where you can toggle the filtration of data between cross-highlight, cross- filtering or no interaction between each visual.
But this does not apply to a slicer visual, which only has cross-filtering and no interaction feature. This made me wonder if we can achieve cross-highlight with a slicer visual through some work-around that allows me to compare one category with other easily through cross-highlight.
After some research, I may have found a way to do this. Below are the steps in detail to achieve this.
Normal filtering of data with slicer:
The below example shows normal slicing of data where only the selected value will be reflected in the bar chart. This does not allow me to compare other values easily or focus in on the selected value through highlighting.
Dynamic highlighting with Slicer:
The below example shows the dynamic highlighting where I can choose the categories in the slicer to highlight for comparison with the other categories. I can easily focus on the selected categories and compare the measure values with other categories.
First, I have created a disconnected table with the categories. This can be easily done with the following dax formula.
Selected Category = VALUES(Orders[Category])
Had made sure there is no relationship in the model view between the source table and new category table.
Created a measure which will be added to the conditional formatting in data colour section in the format pane.
Selected Colour bar = var selected_category = VALUES ('Selected Category'[Category]) var category_to_highlight = SELECTEDVALUE (Orders [Category]) var filtered = ISFILTERED ('Selected Category'[Category]) var result = SWITCH(TRUE(),NOT(filtered),"#0055cc", category_to_highlight in selected_category && filtered,"#0055cc","#9cd0ed") return result
Selected Colour bar =
var selected_category = VALUES ('Selected Category'[Category]) // taking values from category table
var category_to_highlight = SELECTEDVALUE (Orders [Category]) // Using selected value function
var filtered = ISFILTERED ('Selected Category'[Category]) // checks if the column is filtered and will return true or false
var result = SWITCH(TRUE(),NOT(filtered),"#0055cc", category_to_highlight in selected_category && filtered,"#0055cc","#9cd0ed")
/* The first condition checks if the there is no filtration will return all values,
Then will be checking if the selected set of values is contained in the category column.
The selected value will be returning a specific dark colour while the unselected value will be giving a lighter colour. */
Created a bar chart with total sales given in the value section and the category column from the source data will be given as axis.
Gave the highlight effect by adding a measure in the field value section of the data colour conditional formatting.
Added the measure in the field value section.
Added category slicer from the Selected Category table
Finally arranged them and saw the magic happen.
This goes to show the hidden features of Power BI one can explore with a little bit of tinkering with a dash of DAX. This blog is a first in a series of many nifty blogs. Hope you like it and looking forward to your feedback.