Sunday, November 11, 2018

Project Completion KPI Icons for Power BI

Some time ago, I wrote an published some pictures that you can use as URLs to bring into your Power BI Reporting. To recap, I perform the following in order to use the below Icons in Power BI Desktop:

  1. Pull in the data that you need to pivot on into Power BI Desktop
  2. Understand what drive's changing the KPI Icon (e.g., a % or flag of some kind)
  3. Either in the Query Editor side of Power BI or as a calculated column after the data is loaded, we will use an IF statement.
  4. Use the IF statement to derive the URL. Example, if item one if 25% completed, use the URL to the appropriate icon.
  5. Once you've built the new column and the data is loaded, we need to assign that the new column is an Image URL in Power BI Desktop enabling that image to be fetched and displayed in a Matrix visualization.
  6. Column Data Category settings are located in the Modeling tab of Power BI Desktop. Simply select the new column of your data with IF statement of URLs to use, select the Modeling tab in the Power BI Desktop ribbon, and use the drop down in Data Category to select Image URL.
Examining the M formula for Query Editor or the IF statement with DAX as a calculated column:

DAX:
KPI Icon (or your new column name of derived URLs) = if [metric column] <= .25, "https://...  " , if [metric column] >.25 && [metric column] <= .5 , "https://.... " ))

M based formula for Power Query and the Query Editor in PBIX:
Create Custom Column & name it in the popup window, followed by writing the following formula - it is case sensitive by the way.
=if [metric column] <= .5 then "https://... " else if [metric column] >.5 and [metric column] <=.75 then "https://... " else null (you chose the end condition - I often use "Needs Identification" for validation purposes).

The Images - simply right click on each below to obtain the URL. I have no plans on deleting this post ever so.. have fun!

Empty KPI Icon:

25% KPI Icon: 

50% KPI Icon: 

75% KPI Icon: 

100% KPI Icon:  


Status Icon:


IGSS Logo