Display the status of DocRead reading tasks

I'm currently trying to learn the basics of Power BI to try and replicate some of my existing reports. I figured I would learn faster by trying help our customers making the most out of DocRead rather than following a set training course covering information I don't need to know. 

My end goal is to use Power BI to generate custom reports for our DocRead compliance tool. DocRead stores all of its data in a SQL database and has views that have been specifically created for reporting. However, step one is for me to learn how to replicate the existing reports. 

The current data structure

In the DocRead database there are two fields:

  1. The Status field. This is a text field and can either be "assigned" or "completed" and
  2. the Task overdue field. This is a boolean field which is calculated from the due date of the task. It can either be "True" (i.e. overdue because the due date was in the past) or "False" (because the due date had not arrived). 

Are your policies read on time and by the right people?

DocRead makes compliance simple

See how

My requirement

I wanted to create a new field in my database table to aggregate the values from these two fields to provide a single overall status.

The logic goes as follows:

If the DocRead task status was "completed", then the final result needed to be "completed" (comparing to due dates is irrelevant because the task is completed).

For the assigned tasks, there were 2 possibilities. Either 

  1. the due date for the compliance task was past, so the task was "Overdue", or 
  2. the due date was in the future, so the task was "Assigned"

The solution

I knew I needed to use 2 nested IF statements, but the second element was a Boolean field (showing TRUE/FALSE) After many failed attempts to get the IF statement correct, I realised that because the Boolean value was already giving a TRUE/FALSE answer, the answer was simpler than I thought.

= Table.AddColumn(Readership_vwTaskInfo, "RealStatus", each if [Status] = "Completed" then "Completed" else if [Overdue] then "Overdue" else "Assigned")

I've broken this down below:

Element in formula

What it does

Table.AddColumn(Readership_vwTaskInfo, "RealStatus", 

This element adds a column for my new field called "RealStatus"

each if [Status] = "Completed" then "Completed"

This looks for the value in the Status field for each row of my database table independently. If the Status value is "Completed" then it adds the value "Completed" into my new Real Status field

else if [Overdue] then "Overdue"

As the [Overdue] field is already a boolean value and returns either True or False, it doesn't need to be compared to another value to work out whether the answer is True or false, the IF statement can work already. IF the [Overdue] field is true (i.e. it has been flagged as Yes/Overdue in the database) then add the value "Overdue" into the "RealStatus" field

else "Assigned"

The final part of the IF statement describes what to do if neither of the first criteria are met. In my case, I want to give the task an "Assigned" RealStatus.

I was therefore able to create one new field showing whether the compliance tasks sent by DocRead were Assigned, Overdue or Completed and use this information in my Power BI pie chart.

See what DocRead can do for you

Book a custom demo with one of our experts now to discuss your specific requirements and find out how DocRead can help you stay compliant

book now