Today I’m feeling a little overwhelmed by something simple I’m trying to do with a DATEDIFF measure!
Process Time =
DATEDIFF(MIN('StatusHistory'[DateCreated] ), MAX('StatusHistory'[DateCreated]), DAY )
The measure works fine, it returns the total number of days for the required context.
The issue is I need to exclude rows from the table StatusHistory where column Action is equal to “Completed and closed”, below image shows a table where I have filtered on column RequestNumber, where we have a “Completed and closed” in the Action column, I have intentionally only showed one group of records RequestNumber 00488, i have many other instances of like groups in the table.
It’s always a bit of a crapshoot providing DAX solutions without seeing the underlying data model or having a PBIX file to test on, but here goes nothing. See if this works for you:
Brian, thank you for your response, and as you stated somewhat of a crap-shoot to get a solution without an actual pbix file. I have attached a basic file that covers the scope of what I need to achieve.
The image below shows the result of changing the DAX measure to include your suggestion, as you can see it is returning the total for each instance of the request number.
Hopefully having a pbix file will make the potential solution much easier.
Indeed it does. If we add in a variable that tracks the current request number and then uses that as an additional filter condition, I think it now runs like a champ:
Brian, thank you for the response. I added your revised DAX with the VAR for the current request number. I really don’t think it worked the way we planned. For example I can still see (in the visual) request number where we have a corresponding “Completed and closed” for StatusHistory [Action]. So we have not filtered out those records. The code is below
Process Time eDNA BJ =
VAR _CurrentReq = SELECTEDVALUE( 'StatusHistory'[RequestNumber] )
VAR _Result =
CALCULATE(
DATEDIFF(
MIN('StatusHistory'[DateCreated] ),
MAX('StatusHistory'[DateCreated]),
DAY
),
FILTER(
ALLSELECTED( 'StatusHistory' ),
'StatusHistory'[Action] <> "Completed and closed" &&
'StatusHistory'[RequestNumber] = _CurrentReq
)
)
RETURN
_Result
The image below shows the request number 00488, which has a matching “Completed and closed”
The image below shows that request number 00488 is still part of the visual.
One thing I did notice is that the RequestNumber in the visual is actually from the CorrectiveAction table, shown in the image below.
When I did change to the ‘StatusHistory’ [RequestNumber] it still didn’t work, and it also didn’t calculate the measure [Pareto Chart] correctly. Which then prompted me to update where needed all the measures that referenced the ‘CorrectiveAction’ [RequestNumber] to ‘StatusHistory’ [RequestNumber] and change the visual to ‘StatusHistory’ [RequestNumber] , and things went from bad to worse.
Not sure where to go from here. Revised pbix file is attached.
No, the DAX I wrote works perfectly – the problem is it answers completely the wrong question. Sorry – my bad on that one. I totally misinterpreted your requirement. I thought you just wanted to eliminate the row that was closed, and recalculate the DATEDIFF for that request number minus the observations that were closed.
Now I understand that if ANY of the rows are closed for that request number, then you want that request number eliminated entirely from the calculation and the visuals.
Let me rework this – this solution now lends itself much better to a Power Query-based solution than a DAX-based approach.
Be back shortly with a proposed solution. Sorry for the confusion.
Definitely going to want to do this in Power Query. Can you please post the data file?
One other question. From a data modeling standpoint, why is Corrective Action set up as a dimension table? It seems to me that should just be a separate field in the fact table.
Good observation… The Corrective Action table is very much minimized, too much sensitive information in the original file. In my real data the Corrective Action table is used as a lookup table.
data.xlsx is the StatusHistory table
data2.xlsx is the CorrectiveAction table
Then, with some custom M determine whether any record within the nested tables grouped by request number contain the phrase “Completed and closed” in the Action field
