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.
