Creating a SharePoint document library dashboardSharePoint provides excellent opportunities to create document repositories. You ...
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:
- The Status field. This is a text field and can either be "assigned" or "completed" and
- 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?
Make compliance simple
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
- the due date for the compliance task was past, so the task was "Overdue", or
- the due date was in the future, so the task was "Assigned"
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
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
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
June 13, 2022
May 30, 2022
Create a Reporting Hierarchy in Power BI Creating hierarchies in your Power BI data provides ...
May 17, 2022
Advanced DocRead reporting with Power BI The DocRead reporting suite provides several reports to help you manage ...
May 16, 2022
Downloadable background templates for your Power BI reportsPower BI allows you to create amazing ...
May 13, 2022
How to import your DocRead data into Power BIThe DocRead reporting suite provides several ...
May 10, 2022
Keeping track of compliance tasks for new hires with Power BI Ensuring all of your ...