Complex grouping calculation on each date in Matrix Visualization

Ganikuss — Thu, 18 May 2023 14:57:29 GMT

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.

topic Complex grouping calculation on each date in Matrix Visualization in DAX Commands and Tips

Complex grouping calculation on each date in Matrix Visualization