Forum Discussion
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!
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.
1 Reply
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.