Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count first occurance with measure - repost

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.

 

IDDate TimeDateCountCumulative Sum
12346Jan 1st 2021 07:15:32Jan 1st 202111
12346Jan 1st 2021 07:15:58Jan 1st 2021 1
12340Jan 2nd 2021 07:15:34Jan 2nd 202112
12346Jan 2nd 2021 07:15:35Jan 2nd 2021 2
00011Jan 2nd 2021 07:15:36Jan 2nd 202113
55555Jan 2nd 2021 07:15:37Jan 2nd 202114
55555Jan 2nd 2021 07:15:38Jan 2nd 2021 4
55555Jan 2nd 2021 07:15:39Jan 2nd 2021 4
11122Jan 2nd 2021 07:15:40Jan 2nd 202115
11111Jan 2nd 2021 07:15:41Jan 2nd 202116
33333Jan 2nd 2021 07:15:42Jan 2nd 202117
11122Jan 3rd 2021 09:15:43Jan 3rd 2021 7
22222Jan 3rd 2021 09:15:44Jan 3rd 202118
11122Jan 3rd 2021 09:15:45Jan 3rd 2021 8
12346Jan 3rd 2021 09:15:46Jan 3rd 2021 8
00011Jan 3rd 2021 09:15:47Jan 3rd 2021 8
12346Jan 4th 2021 07:15:48Jan 4th 2021 8
12346Jan 4th 2021 07:15:49Jan 4th 2021 8
10101Jan 4th 2021 07:15:50Jan 4th 202119
12341Jan 5th 2021 11:15:51Jan 5th 2021110
12345Jan 5th 2021 11:15:52Jan 5th 2021111
55555Jan 5th 2021 11:15:53Jan 5th 2021 11
12346Jan 5th 2021 11:15:54Jan 5th 2021 11
00011Jan 5th 2021 11:15:55Jan 5th 2021 11
00011Jan 5th 2021 11:15:56Jan 5th 2021 11

 

Example of how Im trying to visualize:

Edw_K_0-1635880587282.png

 

 

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!

1 ACCEPTED SOLUTION

@Anonymous  

 

You can achieve it witht this

 

Measure = 
COUNTROWS (
    FILTER (
        tbl,
        tbl[DateTime] = CALCULATE ( MIN ( tbl[DateTime] ), ALLEXCEPT ( tbl, tbl[ID] ) )
    )
)

 

smpa01_0-1635946220628.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@smpa01 its only showing the rows with 1 values for the measure, but it appears to be fine. Thanks so much!

smpa01
Super User
Super User

@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?

 

smpa01_0-1635884649764.png

 

ID=55555 you are counting them once, on 2nd and why not on 5th?

smpa01_1-1635884730608.png

ID=11122 you counted on all days

smpa01_2-1635884811751.png

 

The counting logic is really not clear. Can you please improve this question?

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

@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] ) )
    )
)

 

smpa01_0-1635946220628.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors