I have a fact table that displays various states of a piece of equipment. I want to find the # of records between two "states" in a table. So you see I have a changeover at 1/11/21 at 9:53:26 and another one at 1/11/21 at 11:17:18. There are 12 records in between these two states:
I am creating some measures to get this count. But I cannot see to get my DAX to pick up the proper number, as it keeps displaying the total number of records in the table for this piece of equipment (L4 Filler).
My measures are as follows:
This State Date/Time =
VAR ThisCODate = SELECTEDVALUE(vw_fact_Utilization[util_start_date])
Return ThisCODate
Next State Date/Time =
VAR CODate = SELECTEDVALUE(vw_fact_Utilization[util_start_date])
VAR COAfterDate = CALCULATE(MIN(vw_fact_Utilization[util_start_date]), FILTER(ALL(vw_fact_Utilization[util_start_date]), vw_fact_Utilization[util_start_date] > CODate))
Return COAfterDate
Total per Changeover =
VAR CODate = SELECTEDVALUE(vw_fact_Utilization[util_start_date])
VAR COAfterDate = CALCULATE(MIN(vw_fact_Utilization[util_start_date]), FILTER(ALL(vw_fact_Utilization[util_start_date]), vw_fact_Utilization[util_start_date] > CODate))
VAR row_count = CALCULATE(COUNTROWS(ALL(vw_fact_Utilization[util_start_date])), FILTER(vw_fact_Utilization, vw_fact_Utilization[util_start_date] > [This State Date/Time] && vw_fact_Utilization[util_start_date] < [Next State Date/Time]))
Return IF(ISBLANK(row_count), 0, row_count)
My "Total per Changeover" column is essentially trying to count rows between the two dates, but using the entire table because the rows inbetween the "Changeover" state are what I want to count (the lines with Producing, Faulted, Starved, etc.). But I also need to filter it by the equipment (L4 Filler). So there are 12 rows between 9:53 and 11:17 changeover records. It doesn't seem to using the FILTER I have in the row_count variable that only picks up the date range using greater and less than.
Please share you example data in a format that can be copied/pasted (either insert a table, or provide a link to it on Google Drive/OneDrive). I started to try to get it through OCR of your image but it didn't work out well.
Microsoft Employee
