Form Completion by Week

Wharfey — Mon, 08 Jun 2020 08:36:36 GMT

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!

Re: Form Completion by Week

technolog — Sun, 17 Sep 2023 17:21:14 GMT

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.

topic Re: Form Completion by Week in DAX Commands and Tips

Form Completion by Week

Re: Form Completion by Week