Hi everyone,
I've got a simple problem that I've solved using Power Query but I don't think it’s a viable long term solution because of the sheer volume of data it produces. What I am looking for is a way to replicate the functionality of my M Query solution but with the finesse of dax.
So, the problem is simple. I have several compliance activities that sites are required to do at varying frequencies, but they all end up generating a window of compliance. Simply put, if its completed or remains incomplete after its due date its red, if its within seven days its amber and if it has got longer time left its green. One of the key requirements of our team is the ability to see what a compliance state was at a particular date and also over time through compliance trend line chart to identify consistent sites and individuals who are struggling to keep their site complaint. It is important to note that the SaaS that I am collecting this data from does not allow us to do this in their product hence the reliance on Power BI for this insight.
To achieve this I work out the start date and the last date and then use the List.Dates function to expand all dates and then I can do a simple dax measure to work out what the compliance percentage is. What I would like to do is to remove the bloat created by using List.Dates and have a completely Dax oriented solution. However, my M is stronger than my dax and whilst I have wrestled with the problem since I’m a one-man band in my organisation there is nobody to bounce an idea or two off. I know I need to expand the dates and test whether the MAX date falls in that range and then if it does to work out what its status is; Red, Yellow or Green and then count number of activities that were active that day and in date over the total number of active activities.
I’ve published a complete dataset as of this morning that has been cleaned of sensitive data and it has been incorporated into my M code below. The query is called "Compliance Activities".
let
Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vTEM3okRxs1s5B6hGMGXcOBRQ8KcrAuFIsf2CYkDTPhxJc2WgkvTH6neS9aZlPwDg/pub?output=xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Priority] <> "") and ([Deleted] <> "1")),
#"Changed Type to DateTime" = Table.TransformColumnTypes(#"Filtered Rows",{{"NextDue", type datetime}, {"DateDueWhenDone", type datetime}, {"DOE", type datetime}, {"DateDeleted", type datetime}}),
#"Changed Type to Date" = Table.TransformColumnTypes(#"Changed Type to DateTime",{{"PropertyKey", Int64.Type}, {"DateDueWhenDone", type date}, {"DateDone", type date}, {"NextDue", type date}, {"FrequencyNumber", Int64.Type}, {"DateDeleted", type date}, {"Status", Int64.Type}}),
#"Added Due Date" = Table.AddColumn(#"Changed Type to Date", "Due Date", each if [DateDueWhenDone] = null then [NextDue] else [DateDueWhenDone]),
#"Added Last Date" = Table.AddColumn(#"Added Due Date", "Last Date", each if [DateDone] <> null and [DateDueWhenDone] = null and [NextDue] >= DateTime.Date(DateTime.LocalNow()) then [NextDue] else if [DateDone] <> null and [DateDueWhenDone] = null and [NextDue] < DateTime.Date(DateTime.LocalNow()) then DateTime.Date(DateTime.LocalNow()) else if [DateDone] <> null then [DateDone] else if [DateDone] = null and [Due Date] < DateTime.Date(DateTime.LocalNow()) then DateTime.Date(DateTime.LocalNow()) else [Due Date]),
#"Added ActivityRecordStartDateTemp" = Table.AddColumn(#"Added Last Date", "ActivityRecordStartDateTemp", each if [FrequencyMeasure] = "Week" then Date.AddWeeks([Due Date],-[FrequencyNumber]) else if [FrequencyMeasure] = "Month" then Date.AddMonths([Due Date],-[FrequencyNumber]) else if [FrequencyMeasure] = "Year" then Date.AddYears([Due Date],-[FrequencyNumber]) else null),
#"Added Activity Record Start Date" = Table.AddColumn(#"Added ActivityRecordStartDateTemp", "Activity Record Start Date", each Date.AddDays([ActivityRecordStartDateTemp],1)),
#"Added Activity Record Status" = Table.AddColumn(#"Added Activity Record Start Date", "Activity Record Status", each if [Status] = 30 then "Closed" else if [Status] = 20 then "Awaiting Documentation" else if [Status] = 10 then "Open" else null),
#"Added Dates to List Column" = Table.AddColumn(#"Added Activity Record Status", "Date", each { Number.From([Activity Record Start Date])..Number.From([Last Date]) }),
#"Expanded Date" = Table.ExpandListColumn(#"Added Dates to List Column", "Date"),
#"Changed Date to Type Date" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}, {"FrequencyNumber", type text}, {"Due Date", type date}, {"DOE", type date}}),
#"Removed null Dates" = Table.SelectRows(#"Changed Date to Type Date", each ([Date] <> null)),
#"Added Overdue" = Table.AddColumn(#"Removed null Dates", "Overdue", each if [Due Date] < [Date] then 1 else 0),
#"Added Compliance Status" = Table.AddColumn(#"Added Overdue", "Compliance Status", each if [Date] > [Due Date] then "Red" else if [Date] > Date.From(Number.From([Due Date])-7) then "Amber" else "Green"),
#"Added Activity Frequency" = Table.AddColumn(#"Added Compliance Status", "Activity Frequency", each if [FrequencyNumber] = "1" then [FrequencyNumber]&" "&[FrequencyMeasure] else [FrequencyNumber]&" "&[FrequencyMeasure]&"s"),
#"Added Compliance Status Sort" = Table.AddColumn(#"Added Activity Frequency", "Compliance Status Sort", each if [Compliance Status] = "Green" then 1 else if [Compliance Status] = "Amber" then 2 else if [Compliance Status] = "Red" then 3 else null),
#"Added Priority Sort" = Table.AddColumn(#"Added Compliance Status Sort", "Priority Sort", each if [Priority] = "High" then 1 else if [Priority] = "Medium" then 2 else if [Priority] = "Low" then 3 else null),
#"Merged PropertyKeys for connected sites" = Table.ReplaceValue(#"Added Priority Sort", 12848, 4328, Replacer.ReplaceValue, {"PropertyKey"}),
#"Removed Other Columns" = Table.SelectColumns(#"Merged PropertyKeys for connected sites",{"PropertyKey", "Activity", "Category", "ActivityRecordKey", "DateDone", "Priority", "Status", "Deleted", "Due Date", "Last Date", "Activity Record Start Date", "Activity Record Status", "Date", "Compliance Status", "Activity Frequency", "Compliance Status Sort", "Priority Sort"}),
#"Renamed Date Done Column" = Table.RenameColumns(#"Removed Other Columns", {{"DateDone", "Date Done"}}),
#"Removed Dates Prior to Trust Formation" = Table.SelectRows(#"Renamed Date Done Column", each [Date] > #date(2018, 9, 1)),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Dates Prior to Trust Formation", {"Compliance Status Sort"}),
#"Transform columns" = Table.TransformColumnTypes(#"Removed Errors", {{"Category", type text}, {"Activity", type text}, {"Activity Frequency", type text}, {"Compliance Status", type text}, {"Priority", type text}, {"Status", type text}, {"ActivityRecordKey", type text}, {"Activity Record Start Date", type text}, {"Last Date", type text}, {"Activity Record Status", type text}, {"Compliance Status Sort", type text}, {"Priority Sort", type text}})
in
#"Transform columns"
The dax measure I use to calculate the compliance is as follows:
Compliance Percentage =
var ComplianceNumerator =
Calculate(
DISTINCTCOUNT('Compliance Activities'[ActivityRecordKey]),
('Compliance Activities'[Compliance Status] IN {"Green", "Amber"}),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] = MAX ( 'Calendar'[Date] )
))+0
var ComplianceDenominator =
CALCULATE(DISTINCTCOUNT('Compliance Activities'[ActivityRecordKey]),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] = MAX ( 'Calendar'[Date] )
))
Return
CALCULATE(
DIVIDE(
ComplianceNumerator,
ComplianceDenominator
)
)
