First of all, happy Friday! As we get ready to enjoy the weekend, I thought of noting down a quick tip on how to use the totally awesome Chiclet Slicer to display custom indicators in Power BI. If you are hearing about the Chiclet Slicer for the first time, please do check out the official Microsoft blog on this as it is a very useful viz. For people who follow my blogs, you would remember that I had already written down a technique to create Indicators in Power BI before. But the main drawback in that approach was that there was no way to color the indicators, and also we were limited by the set of Unicode characters that could be used as indicators. With the advent of the chiclet slicers, we can now dynamically display any image as our indicator and this post will precisely show you how to do it. For this demo, let’s say – I want to display a green up arrow or a red down arrow based on whether my measure is positive or negative. For that, follow the steps below:-
1) Open the Power BI desktop file where you want to add the indicator, and then go the data tab. Click on the New Table button.
2) It is important to understand that the chiclet slicer, just like the regular slicer, can only display table fields or calculated columns (and not measures). So we have to create a table with a list of all the “states” or possible values. In my case, we can have only 2 states – Up and Down. Use DAX to create a table with 2 rows – Up and Down. Also, add the image url for each of the state (in my case, an image url for the up and down arrows).
Indicator =
UNION (
ROW ( “Indicator”, “Up”,
“ImgURL”, “http://www.clipartbest.com/cliparts/nTX/EGB/nTXEGBLTB.png” ),
ROW ( “Indicator”, “Down”,
“ImgURL”, “http://www.clker.com/cliparts/D/8/S/c/z/3/red-down-arrow-md.png” )
)
Note that we are making use of the calculated table feature in Power BI to create a table with a list of states.
3) Let us say that I have a measure called Metric which shows either positive or negative value. Right now, I am just hardcoding it to -30.
4) Now create a new measure which will display 1 for Up if the measure Metric is >=0 or display 1 for Down if the measure Metric is < 0
LinkedMeasure =
SUMX (
VALUES ( Indicator ),
IF (
(
[Metric] >= 0
&& VALUES ( Indicator[Indicator] ) = “Up”
)
|| (
[Metric] < 0
&& VALUES ( Indicator[Indicator] ) = “Down”
),
1
)
)
5) On the Report tab, add the Indicator column and the Linked Measure to the canvas, and then convert it into a chiclet slicer (make sure you download and import this custom visualization from the Power BI Visuals Gallery before this step). Also add the ImgURL field to the Image field. You can change the Image Split property under the Image section to 100 from the default 50, so that the Image occupies 100% of the space
6) Hide the borders and also turn off the headers, so that only the image is visible.
7) Now you can add a textbox besides the chiclet slicer to display the metric. Now go ahead and change the values of the metric, and you can see the chiclet slicer automatically update itself with the right indicator.
The chiclet slicer is pretty good on it’s own as a way to slice data, but the ability to display custom images takes it to the next level. You can use it for a lot of tips and tricks, and I hope this post gets you thinking on what all you can do with this. And there goes your weekend, BOOM!
Note
As usual, make sure you look at the date at which this post was published and the version of Power BI. Since Power BI has a rapid release cycle, I would expect some of the features to change. Hence, always check whether a new feature makes it more easier to implement your scenarios like this one. The version I used is given below.