Hi Community,
I am trying to use Power BI Desktop to add some analytic capabilities in my project and need assistance with measuring DAX expression
Here is my data topology
| Country | Status | Year | ConfigId | ByDate | RowNumber | UserId | LastupdatedUTC |
| USA | Pending | 2022 | 1 | 01/01/2022 | 1 | 1 | 01/01/2022T00:00:00 |
Users with UserId from Country can request report generation with ConfigId for Year
Then report flows Requested -> Pending -> Completed/Failed it can be done in few days for 1 user
Having a table with provided topology for all users I need to Calculate stats on each day:
1. Reports count rows By Country, Year, Status, ConfigID on each day district by userId
So, in general, I need to have a matrix visualization with Country, Year, Status, ConfigID as rows and ByDate as columns, values should be calculated following logic:
1. Set ByDate as a boundary, filter about all records after ByDate
2. Group filtered dataset by Country, UserId, Year, ConfigId and select the top 1 record from each group ordered by LastUpdatedUtc
3. Result of grouping and selecting top 1 record from each group should be grouped a second time by Country, Year, Status, ConfigId and calculate rows count of each group
As a result, measure DAX should return the cumulative state of reports generation history on each day
Issue: in matrix visualization can not find a way to use ByDate Column just like a boundary but not include it in the group by DAX
Please assist if it is possible or suggest please a better solution.
