On the surface, Power BI might seem like a simple reporting tool offering a set of common visuals and capabilities to transform data into insights. But once you start exploring the tool, you will find that it’s the magic wand to unlock those amazing reporting features every business has been looking for. All you need to know is the right magic spell. The past few months of my journey through Power BI has been devoted to the research and discovery of these spells.
In this blog post which is the first of a two-part series, I will cover one of the tricks that I have learnt to dynamically change the trend indicators for KPIs — none that involves complicated DAX measures that a beginner cannot crack.
I have noticed many Power BI reports flaunting this cool feature where the status trend image dynamically changes between green and red based on the value of a measure. For example, if there has been an increase in sales compared to last year (YoY % growth), a green trend-up image will be displayed, and a red trend-down otherwise.
Currently, Power BI does not support conditional formatting for images to achieve this. Thus, the only solution is to adopt a DAX-based approach. Many articles out there on the internet have covered various DAX based solutions, all of which were rather difficult to crack. The workaround I will showcase below is simple.
Have a separate table for the indicator images with a column for the image index.
Change the data category for the URL column to ‘Image URL’ after loading the data. This is essential as otherwise, Power BI would display the images as plain text.
The next step was to create the aggregate measures based on which the conditional formatting will be applied. I created three simple measures for the total sales and previous year sales to calculate the YoY % change.
The actual challenge now was to figure a logic to dynamically display the indicator images based on a condition. To address this scenario, it took only two simple measures.
The Indicator Flag measure below uses a simple IF function to assign one of the two image indices from the image table. This measure returns:
- ‘1’ for the green ‘Up’ arrow if the YoY change is greater than or equal to ’0’
- ‘2’ for the red ‘Down’ arrow if the YoY change value is less than’0’.
The last step was to create the measure for the dynamic indicator itself. The key here was to use the basic LOOKUPVALUE function to get the image URL from the image table for the image index returned by the Indicator Flag measure.
Change the Data Category for this measure to ‘Image URL,’ and we are good to go.
Currently, there are no custom visuals that can display image measures. Therefore, I resorted to using the table visual. With a little tweaking and playing with the properties of this visual, the image was displayed neatly alongside the card visual that showed the YoY change.
To further test the accuracy of this approach, I added a product slicer to see if filtering on a product gave the desired results for the image indicator for the YoY change. The image reflects the expected results when the slicer selection for product changes.
Thus, we could dynamically display the indicator images based on any condition of our choice and business users can easily see the status of key performance indicators at a glance.
That’s it for now, folks. Stay tuned for my next blog where I will cover a few more DAX tricks to change visuals dynamically.
Thanks for reading!
