Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I would like to achieve the Count column using a measure. only counting the first occurance of an ID within the time series. It must be with a measure, I know how to do this with a column but need a measure so I can dynamically change the date selection and have it adjust to count occurances.
ID | Date Time | Date | Count | Cumulative Sum |
12346 | Jan 1st 2021 07:15:32 | Jan 1st 2021 | 1 | 1 |
12346 | Jan 1st 2021 07:15:58 | Jan 1st 2021 | 1 | |
12340 | Jan 2nd 2021 07:15:34 | Jan 2nd 2021 | 1 | 2 |
12346 | Jan 2nd 2021 07:15:35 | Jan 2nd 2021 | 2 | |
00011 | Jan 2nd 2021 07:15:36 | Jan 2nd 2021 | 1 | 3 |
55555 | Jan 2nd 2021 07:15:37 | Jan 2nd 2021 | 1 | 4 |
55555 | Jan 2nd 2021 07:15:38 | Jan 2nd 2021 | 4 | |
55555 | Jan 2nd 2021 07:15:39 | Jan 2nd 2021 | 4 | |
11122 | Jan 2nd 2021 07:15:40 | Jan 2nd 2021 | 1 | 5 |
11111 | Jan 2nd 2021 07:15:41 | Jan 2nd 2021 | 1 | 6 |
33333 | Jan 2nd 2021 07:15:42 | Jan 2nd 2021 | 1 | 7 |
11122 | Jan 3rd 2021 09:15:43 | Jan 3rd 2021 | 7 | |
22222 | Jan 3rd 2021 09:15:44 | Jan 3rd 2021 | 1 | 8 |
11122 | Jan 3rd 2021 09:15:45 | Jan 3rd 2021 | 8 | |
12346 | Jan 3rd 2021 09:15:46 | Jan 3rd 2021 | 8 | |
00011 | Jan 3rd 2021 09:15:47 | Jan 3rd 2021 | 8 | |
12346 | Jan 4th 2021 07:15:48 | Jan 4th 2021 | 8 | |
12346 | Jan 4th 2021 07:15:49 | Jan 4th 2021 | 8 | |
10101 | Jan 4th 2021 07:15:50 | Jan 4th 2021 | 1 | 9 |
12341 | Jan 5th 2021 11:15:51 | Jan 5th 2021 | 1 | 10 |
12345 | Jan 5th 2021 11:15:52 | Jan 5th 2021 | 1 | 11 |
55555 | Jan 5th 2021 11:15:53 | Jan 5th 2021 | 11 | |
12346 | Jan 5th 2021 11:15:54 | Jan 5th 2021 | 11 | |
00011 | Jan 5th 2021 11:15:55 | Jan 5th 2021 | 11 | |
00011 | Jan 5th 2021 11:15:56 | Jan 5th 2021 | 11 |
Example of how Im trying to visualize:
If I do a simple distinct of the ID column I can easily return the 11 unique IDs, but the challenge is showing how this 'adds up' in a time series like above in form of a measure (NOT a column) so I can flexibly change the date.
Help is very appreciated!
Thanks!
Solved! Go to Solution.
@Anonymous
You can achieve it witht this
Measure =
COUNTROWS (
FILTER (
tbl,
tbl[DateTime] = CALCULATE ( MIN ( tbl[DateTime] ), ALLEXCEPT ( tbl, tbl[ID] ) )
)
)
@smpa01 its only showing the rows with 1 values for the measure, but it appears to be fine. Thanks so much!
@Anonymous counting the first occurence of ID?
Your desired out put does not reciprocate that
for e.g. for ID=12346 you are counting them twice, on 1st and 3rd and why not on any other days?
ID=55555 you are counting them once, on 2nd and why not on 5th?
ID=11122 you counted on all days
The counting logic is really not clear. Can you please improve this question?
@smpa01 built it from some dummy data very quickly - I corrected it now.
Yes, I need to count the first occurance of the ID (in order of the datetime) with using a measure so it can handle different date ranges, and not be fixed like a column.
@Anonymous
You can achieve it witht this
Measure =
COUNTROWS (
FILTER (
tbl,
tbl[DateTime] = CALCULATE ( MIN ( tbl[DateTime] ), ALLEXCEPT ( tbl, tbl[ID] ) )
)
)
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |