Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

ankitpatira

SSRS like traffic light indicators in Power BI

 

As I remember managers used to love SSRS reports for their availability of traffic light indicators which are useful way of measuring KPIs, trends etc. During my recent Power BI engagements with clients I have been asked few times if similar can be achieved in Power BI and my answer to that was, yes by pinning SSRS reports into Power BI which is essentially using SSRS and only with version 2016 but during the process I also discovered alternative approach which I want to highlight below.

 

As of today, no such custom or standard visual exists that can be used to show SSRS like traffic light indicators and so the alternative approach would be to use combination of custom image and DAX measures to achieve SSRS like functionality. There is also option to use out of the box feature of conditional formatting which can colour code the column itself but that is not SSRS like.

 

 

 

1 Sample requirement and data

 

Consider we have sample data showing customer service requests logged by members of the public relating to waste, graffiti, parking, roads and traffic etc. in a public space for the city of Melbourne. As per requirement in report we must indicate whether resolution time was within agreed SLAs or not and what better way than to indicate that via traffic light indicators.

 

So using Power BI standard table visual we will show list of issues and traffic light indicators against each.

 

1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2 Traffic Light Images

 

First step is to create and host images for traffic lights indicators online that can be accessed via URL. This includes images of traffic light indicators that we will make use of within table visual. The trick here is rather than importing those images into Power BI we will host them online so that it can be accessed via URL. For this demo purpose, we will use OneDrive and host our images there. After uploading images to OneDrive generate embed codes for each which will provide HTML code that can be used via Power BI.

 

2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3 Conditional Column

 

After importing dataset into Power BI next step is to create a conditional column that will display traffic light indicators based on the business logic. For this demo business logic is to represent number of days it took to resolve customer issue through traffic light and lesser the number of days the better but any query more than 7 days old would be considered not good for the business. So we will create a DAX conditional column as below.

 

3.png

 

As last step change the Data Category for new created conditional column to Image URL.

 

4.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4 Visual    

 

Finally drop the columns to table visual and adjust height of the image via Format tab to fit accordingly.

 

5.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As you can see since we are using custom images here you can use all sorts of indicators / symbols etc to best serve your purpose.

Comments

Hey, @ankitpatira , that's really smart!

It would fit perfectly to my latest blogpost about KPIs in PowerBI, but there I need it as a measure. Unfortunately then the "Data category" is greyed out, so cannot format it accordingly.

Do you have any idea how to solve that?

Thx, Imke

@ImkeF thanks! Data Category is only available for calculated columns so you'll have to change from measure.

@ImkeF sure, voted for it.

Anonymous

Nice.

Anonymous

@ImkeF 

For measures - try using UNICHAR...

See this for reference:

https://community.powerbi.com/t5/Data-Stories-Gallery/Sales-Scorecard-Where-are-we-losing-money-by-D...

Haven't tried this myself, but looks to me as a very nice workaround...

Michael

Yes, thx @Anonymous. I've used them in the article I've mentioned above as well, they are working fine 😉

Just that the choice is a bit limited. So it would have been great being able to just use own pics. 

Cheers, Imke

perfect! 🙂