Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |