topic Count first Occurance with measure in DAX Commands and Tips

Count first Occurance with measure

Anonymous — Thu, 28 Oct 2021 14:17:59 GMT

Hi,

Im stuck, calling DAX experts help.

I need to achieve:

Count of Earliest ID (first ID) and visualize it on the date it occured on.

For example, If 1400 Unique IDs are generated over 2 months, I need to know 86 occured on X day and 302 occured on Y day. Where X and Y should be indepedent / exclusive from each other.

I plan to filter this dynamic by date, so the time range will change and hence a column isnt an option. I have tried with classic DAX DistinctCounts and also with Virtual Tables. In both cases I am overcounting as when I visualize it, it takes the Unique/Dinstinct Count for that day, rather than breaking down the total distinct count number and putting them in groups where they first occured in the time series.

Here's the code Ive tried:

x_test =

var __table = 'fact_Entry Log'

var __table1 = ADDCOLUMNS(__table, "Column",

IF (

CALCULATE (

COUNTROWS ( 'fact_Entry Log' ),

FILTER (

ALLEXCEPT ( 'fact_Entry Log', 'fact_Entry Log'[Personal ID] ),

'fact_Entry Log'[date_key] <= EARLIER ( 'fact_Entry Log'[date_key] )

)

> 1,

) )

VAR calc = COUNTROWS(FILTER(__table1, [Column] = 0) )

RETURN

calc

Re: Count first Occurance with measure

Greg_Deckler — Thu, 28 Oct 2021 14:29:27 GMT

Anonymous Seems like it should be something like:

Measure = VAR __Min = MIN('Table'[Date]) RETURN COUNTROWS(FILTER('Table',[Date]=__Min))

Re: Count first Occurance with measure

Anonymous — Thu, 28 Oct 2021 15:20:47 GMT

Greg_Deckler nope. I need to consider 2 arrays: IDs and Dates. This just has dates in the logic.

Also, this doesnt solve the independence issue, which is that on a time series day-by-day it only takes the first unique ID for that day (so its correct for each day) Im trying to vizualize it over the whole time series. Example, 1400 IDs in the last 2 months, I wont to show 86 of these IDs first occured on week 1, 320 in week, and the sum of all of these = 1400.

This graph shows whats wrong. It shows one each day the number of distinct IDs there are, which can also occur in other day. This is what Im trying to model out.

Make sense? I can explain further or send some sample data if that helps

Re: Count first Occurance with measure

Greg_Deckler — Thu, 28 Oct 2021 16:18:06 GMT

Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Count first Occurance with measure

Anonymous — Mon, 01 Nov 2021 13:16:33 GMT

Here some sample data using 11 unique IDs over a 5 day time range.

I would like to achieve the Count column in form of the a measure. Again, only counting the first occurance of an ID within the time series.

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	1	8
22222	Jan 3rd 2021 09:15:44	Jan 3rd 2021	1	9
11122	Jan 3rd 2021 09:15:45	Jan 3rd 2021	1	10
12346	Jan 3rd 2021 09:15:46	Jan 3rd 2021	1	11
00011	Jan 3rd 2021 09:15:47	Jan 3rd 2021		11
12346	Jan 4th 2021 07:15:48	Jan 4th 2021		11
12346	Jan 4th 2021 07:15:49	Jan 4th 2021		11
10101	Jan 4th 2021 07:15:50	Jan 4th 2021	1	12
12341	Jan 5th 2021 11:15:51	Jan 5th 2021		12
12345	Jan 5th 2021 11:15:52	Jan 5th 2021		12
55555	Jan 5th 2021 11:15:53	Jan 5th 2021		12
12346	Jan 5th 2021 11:15:54	Jan 5th 2021		12
00011	Jan 5th 2021 11:15:55	Jan 5th 2021		12
00011	Jan 5th 2021 11:15:56	Jan 5th 2021		12

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 'add up' in a time series like above in form of a measure so I can flexibly change the date.

Thanks!

Re: Count first Occurance with measure

v-yanjiang-msft — Thu, 04 Nov 2021 09:10:30 GMT

Hi Anonymous ,

According to your description, here’s my solution, M1 is a measure.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.