Hello all,
I’m trying to calculate the amount of work (per day,per status, per other field that is present in table), but I’m struggling a bit with DAX. I have this table that shows you some columns of the source that I have and I added to the table some colors representing the unique records that should be considered for the calculation. Take into account that “Status” Column does not exist in the source, it’s there to give more context to the “Last Status” column.
Date | WorkID | Status | LastStatus | Row Count |
01/02/2024 | 1 | Accepted | 4 | 1 |
02/02/2024 | 1 | In progress | 3 | 1 |
02/02/2024 | 2 | Accepted | 4 | 1 |
02/02/2024 | 2 | In progress | 3 | 1 |
03/02/2024 | 1 | Review | 2 | 1 |
03/02/2024 | 1 | Complete | 1 | 1 |
03/02/2024 | 2 | Review | 2 | 1 |
04/02/2024 | 2 | Complete | 1 | 1 |
The table is showing a list of 2 tasks that are required to be completed and they have 4 stages (accepted, in progress, review and done). The column “Last Status” is a column that exists in the source and it represents the last status number of each one of the explained above (accepted = 4; …; done = 1). There’s also a column called “Row Count” that is always equal to 1, so you can use that to sum rows.
I want to calculate the amount of work and I’m trying to use the Min of “Last Status” as a filter, so I can get a unique record of WorkID for each day, however it’s not working. Below, the current formula…
--
Amount of Work =
VAR Result = CALCULATE(Sum('Table1'[ROW_COUNT]),
KEEPFILTERS(
FILTER(
'Table1',
'Table1'[Last Status] = Min('Table1'[Last Status])
)
)
)
return Result
--
Below the results per day that I received
Date | Measure |
01/02/2024 | 1 |
02/02/2024 | 2 |
03/02/2024 | 1 |
04/02/2024 | 1 |
Looking at the results, we can see that for the 3rd of February, a task is ignored from the calculation and I think the current formula that I have is getting the Min of “Last status” for the entire day and brings only records that have that value for Last status. On the 3rd of February, the Min Last status is 1 and only 1 row is considered, although there’s another work task that I want to consider.
The correct table should be this:
Date | Measure |
01/02/2024 | 1 |
02/02/2024 | 2 |
03/02/2024 | 2 |
04/02/2024 | 1 |
Can you help me understand where I can improve the formula? If yes, please let me know where and if there are important concepts that I may be missing…
Please note this:
-there are multiple formulas that depend on this one, so I don’t think it’s something that should be fixed with the visual;
- I must use “Last Status” column, because this table is connect to the dimensional table of status.
Thanks for reading my questions. Have a good one!
