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.
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.
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.
As last step change the Data Category for new created conditional column to Image URL.
Finally drop the columns to table visual and adjust height of the image via Format tab to fit accordingly.
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.