Power BI: A simple trick to present KPI's and RAG status in tables

  As some of you might already know, Power BI is a fantastic tool for interactive reports and dashboards. Although, if you've been doing some heavy lifting with it, you might also know that it has its limitations.

  In order to help you with one of them, I'll describe a step-by-step approach to display KPI's or RAG status in simple tables.

Scenario:

  I will be using data pulled from project online, in this specific case, tasks. Each task has a RAG status value in a field called 'RAGSched' (be aware that this is a custom field, hence it can have any other name). The icons I'm going to use, also belong to PWA and are stored in the '_layouts/15/inc/PWA' folder.

  To start, I'm going to create a static table to store the icon's name and url, then, I'm going to create a function to retrieve the url passing the icon's name as a parameter and finally apply a bit of logic in a new field, to get the right image according to the RAG field's values.

1) Tenant and Site Collections parameters - as a good practice :)

2) The static table

This will be just a simple table with the relative path to the icons.

3) The function

Ok, now let's select 'New Source' > 'Blank Query' and then click the 'Advanced Editor' button to insert our DAX function:

let

    IconUrl = (IconName as text) => 

    let

        Source = List.First(Table.Column(Table.SelectRows(Icons, each [IconName] = IconName) as table, "ImageName" as text)),

#"CalculatedUrl" = "https://" & Tenant & ".sharepoint.com" & SiteCollection & "/" & Source

    in

        CalculatedUrl

in

    IconUrl

I called it 'GetIconUrl'.

4) Getting the data and adding the new RAG column

As mentioned before, I'm going to be pulling data from PWA, which means I will add a new OData Feed query. Since it's created in the advanced editor mode, I'll break it step-by-step.

Source = OData.Feed("https://" & Tenant & ".sharepoint.com" & SiteCollection & "/_api/ProjectData/Tasks?$select=TaskId,ProjectId,TaskName,RAGSched,TaskComments"),

Here we define the source, using our parameter 'Tenant' and 'SiteCollection' to build the complete url. I'm querying the tasks feed and selecting the fields I want to work with (notice that 'RAGSched' is the field I'll be looking at).

#"Tasks - Add RAG Column" = Table.AddColumn(Source, "RAG Status", each 

if (Text.Length([RAGSched]) > 0)

                then

  if Text.Contains([RAGSched], "Completed") 

then GetIconUrl("Milestone-Completed")

else

if Text.Contains([RAGSched], "On Schedule")

then GetIconUrl("Milestone-OnSchedule")

else

if Text.Contains([RAGSched], "not critical")

then GetIconUrl("Milestone-NonCritical")

else

GetIconUrl("Milestone-Critical")

else 

"")

The second part will add the new column 'RAG Status' and set the icon url according to the RAG field's value.

Here's the complete code block for the query:

let

    Source = OData.Feed("https://" & Tenant & ".sharepoint.com" & SiteCollection & "/_api/ProjectData/Tasks?$select=TaskId,ProjectId,TaskName,RAGSched,TaskComments"),

    #"Tasks - Add RAG Column" = Table.AddColumn(Source, "RAG Status", each 

if (Text.Length([RAGSched]) > 0)

                then

  if Text.Contains([RAGSched], "Completed") 

then GetIconUrl("Milestone-Completed")

else

if Text.Contains([RAGSched], "On Schedule")

then GetIconUrl("Milestone-OnSchedule")

else

if Text.Contains([RAGSched], "not critical")

then GetIconUrl("Milestone-NonCritical")

else

GetIconUrl("Milestone-Critical")

else 

"")

in

    #"Tasks - Add RAG Column"

At this point, if everything went as expected, the new field will show the icon's url:

And this is how your 'Edit Queries' window should look like:

5) Applying the right data category

The only thing missing now is to tell Power BI that our new column contains an image url. To do that, just navigate to your data, select the new column (RAG Status), click on 'Modeling' > 'Data Category' and select 'Image URL'.

Now go to your report's page, add the table visual, select the columns you want to show and apply some styling... it should look similar to the following table:

Hope it helps!!!