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.
I have two tables.
For each date from the date column, I would like to count for each status group the count of users that fall in that status.
Basically what is the most recent status up to each date of the row context.
For example, if someone was:
I want my matrix to show case the following
Date | Awake | Asleep | SleepWalking |
1/1/2020 | 1 | 0 | 0 |
1/2/2020 | 1 | 0 | 0 |
1/3/2020 | 0 | 1 | 0 |
1/4/2020 | 0 | 1 | 0 |
1/5/2020 | 0 | 1 | 0 |
1/6/2020 | 0 | 1 | 0 |
1/7/2020 | 0 | 1 | 0 |
1/8/2020 | 0 | 1 | 0 |
1/9/2020 | 0 | 1 | 0 |
1/10/2020 | 0 | 0 | 1 |
Obviously what i currently have when i activate the Show no data option from the date column
Date | Asleep | Awake | SleepWalking |
1/1/2022 | 1 | ||
1/2/2022 | |||
1/3/2022 | 1 | ||
1/4/2022 | |||
1/5/2022 | |||
1/6/2022 | |||
1/7/2022 | |||
1/8/2022 | |||
1/9/2022 | |||
1/1/2022 | 1 |
How do i fill in the gaps ? One important thing to note is that I don't want to cross join my table with every date possibility even though it would work since my table would generate over 100M rows by doing this.
Solved! Go to Solution.
I used my own data, so the figures are correct but different from what you pasted above.
Measure:
The measure counts the number of visible users that have their latest status in the set of the visible statuses.
If the above solves your problem, please mark it as THE solution so that others have an easier time finding answers. Thanks.
I used my own data, so the figures are correct but different from what you pasted above.
Measure:
The measure counts the number of visible users that have their latest status in the set of the visible statuses.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |