Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
The target is to count the most recent activity status by date. I currently have COUNTROWS of a table and that is correctly giving me all status categorized by dates but there is a new requirement only asking for a total count of the most recent activity status. Any suggestions on how to structure a measure that will result in a count of the most recent activity status?
Below is sample data: table 1 = source data / table 2 = expected output
(Table 1 = source data)
Date | Store_ID | Activity_Status |
Wednesday, October 28, 2020 | 1 | New |
Thursday, October 29, 2020 | 1 | In Progress |
Friday, October 30, 2020 | 1 | Complete |
Friday, January 29, 2021 | 2 | New |
Monday, February 1, 2021 | 3 | New |
Monday, February 1, 2021 | 4 | New |
Tuesday, February 2, 2021 | 3 | In Progress |
(Table 2 = expected output)
Month_Year | Most_Recent_ActivityStatus_Count |
October 2020 | 1 |
January 2021 | 1 |
February 2021 | 2 |
Your advice will be greatly appreciated.
Solved! Go to Solution.
Thank you for your support. I was able to figure out a measure to give me the count of the most recent activity with the following:
Store ID Count = COUNTROWS(DataTable)
Most Recent Activity Count =
CALCULATE(
[Store ID Count],
FILTER(
DataTable,
MAX(DataTable[Store_ID])
),
LASTDATE(DataTable[Date])
)
My problem now is that when I drop this information into a matrix table, the individual activities are still visable. Although the count is correct, is there a way for me to not have the individual activities visable?
Your advice is greatly appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |