topic Re: How to distinct count IDs based on first Occurance date and visualize it in DAX Commands and Tips

How to distinct count IDs based on first Occurance date and visualize it

Anonymous — Tue, 28 Nov 2023 14:09:28 GMT

Dear All,

I am not able to find a solution to following issue

I have a table with multiple columns - with ID, date, description, ...

I need to calculate the total distinct IDs - for only the earliest occurance by date - so I can visualize it per year/month based only on the earliest occurance.

Now the column chart gets mixed up with the dates.

ID	Date	Description	...
1	11/11/2023	Not relevant
1	12/11/2023
2	10/11/2023
2	11/11/2023
3	5/11/2023
4	6/11/2023

example graph

Hope it is a bit clear.

Kind regards

Re: How to distinct count IDs based on first Occurance date and visualize it

FreemanZ — Tue, 28 Nov 2023 14:28:14 GMT

hi Anonymous ,

what do you mean by "earliest occurance by date "?

Re: How to distinct count IDs based on first Occurance date and visualize it

Anonymous — Tue, 28 Nov 2023 14:53:11 GMT

The earliest date the ID occurs in the table

Re: How to distinct count IDs based on first Occurance date and visualize it

FreemanZ — Wed, 29 Nov 2023 02:23:38 GMT

hi Anonymous ,

Could you enrich your sample data to well reflect your case and with the corresponding expected result?

Re: How to distinct count IDs based on first Occurance date and visualize it

Anonymous — Wed, 29 Nov 2023 07:44:30 GMT

Hello

Table1

ID	Operation	Timestamp	Description ...
1	start	11/11/2023
1	stop	11/11/2023
1	start	12/11/2023
2	start	14/11/2023
2	stop	15/11/2023
3	start	20/11/2023
3	stop	21/11/2023
3	start	22/11/2023
4	start	15/11/2023
4	stop	16/11/2023

result

ID	operation	Time
1	Start	11/11/2023
2	start	14/11/2023
3	start	20/11/2023
4	start	15/11/2023

COUNT total IDs

Afterwards

Use count IDs in graph in y-axis

and use timestamps of only table 2 for X-axis (linked with date table to show by year/month)

the one from table two will all be seen in the 2023/11 column (block of 4 - since total is 4 units)

Re: How to distinct count IDs based on first Occurance date and visualize it

FreemanZ — Wed, 29 Nov 2023 08:36:40 GMT

hi Anonymous ,

Could you enrich your sample data to well reflect your case and with the corresponding expected result?

e.g. if id 1 appeared in Nov and Dec, is it counted only in Nov or?

Re: How to distinct count IDs based on first Occurance date and visualize it

Anonymous — Wed, 29 Nov 2023 10:56:45 GMT

so i have these ID with that earliest event - this date is related to a data table with Year/month column. But I am not able to Visualise it because indeed units that start in oktober are seen in November and vice versa