Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wharfey
Frequent Visitor

Form Completion by Week

Morning All,

 

I'm after some help please...Our Engineers are required to complete a weekly H&S check of their vehicles (brakes, battery, tyres etc). They complete this via our own mobile app and the data is pushed into a Sharepoint list which I'm about to connect to PowerBI. The Ops Director has asked for a simple report that identifies of all of the Engineers, who completed the weekly check and who didn't? So essentially I need DAX/visual/whatever to identify a RAG green or red rating on complete/incomplete forms by week, please?

 

Many thanks in advance!

1 ACCEPTED SOLUTION
technolog
Super User
Super User

Alright, let's break this down. First, you'll want to connect your SharePoint list to Power BI. Once you've done that, you'll have a table, let's call it 'EngineerChecks'.

Now, assuming there's a date column in 'EngineerChecks' that indicates when the check was completed, you can create a new calculated column in Power BI to determine the week number. You can use the WEEKNUM function in DAX for this. Here's how you can do it:

WeekNumber = WEEKNUM(EngineerChecks[DateColumn])
Replace DateColumn with the name of your date column.

Next, you'll want to create a measure to determine if an engineer has completed their check for a particular week. This can be done using the COUNTROWS function. Here's a simple measure:

ChecksCompleted = COUNTROWS(EngineerChecks)
Now, to determine the RAG rating, you can create another measure. If the count of rows for an engineer in a particular week is greater than 0, it's green; otherwise, it's red.

RAGRating =
IF([ChecksCompleted] > 0, "Green", "Red")
Now, in your report, you can create a matrix visual. Place the engineer names on the rows, the WeekNumber on the columns, and the RAGRating measure in the values. This will give you a matrix where each cell represents whether an engineer completed their check for a particular week (Green) or didn't (Red).

Lastly, you can use conditional formatting in the matrix visual to actually color the cells green or red based on the value.

View solution in original post

1 REPLY 1
technolog
Super User
Super User

Alright, let's break this down. First, you'll want to connect your SharePoint list to Power BI. Once you've done that, you'll have a table, let's call it 'EngineerChecks'.

Now, assuming there's a date column in 'EngineerChecks' that indicates when the check was completed, you can create a new calculated column in Power BI to determine the week number. You can use the WEEKNUM function in DAX for this. Here's how you can do it:

WeekNumber = WEEKNUM(EngineerChecks[DateColumn])
Replace DateColumn with the name of your date column.

Next, you'll want to create a measure to determine if an engineer has completed their check for a particular week. This can be done using the COUNTROWS function. Here's a simple measure:

ChecksCompleted = COUNTROWS(EngineerChecks)
Now, to determine the RAG rating, you can create another measure. If the count of rows for an engineer in a particular week is greater than 0, it's green; otherwise, it's red.

RAGRating =
IF([ChecksCompleted] > 0, "Green", "Red")
Now, in your report, you can create a matrix visual. Place the engineer names on the rows, the WeekNumber on the columns, and the RAGRating measure in the values. This will give you a matrix where each cell represents whether an engineer completed their check for a particular week (Green) or didn't (Red).

Lastly, you can use conditional formatting in the matrix visual to actually color the cells green or red based on the value.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors