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 distinct values over the period of time (week, month)

Hi guys,

 

I want to see number of unique IDs over the period of time I choose.

SO I have a dataset like this

Date - ID

1.1.2019 - ABC

1.1.2019 - EFG

1.1.2019 - JKL

1.2.2019 - YXZ

1.2.2019 - ABC

So when I filter in my table the date 1.1.2019 (or month January) I will get number 3, but when I filter January+February I get 4 (because in February a new user YXZ came). I thought that creating week/month/quarter columnes and Count (Distinct) will get me that but it just count users distinctly by days.

Any ideas how to do it "dynamically"?

Thank you

 

Lukas

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @Anonymous 

 

I'm not quite sure that I understand your issue correctly, but I manage to get a distinct count on the same data as you. I have created a date table as well which I use as the dimension.

 

distinct users = DISTINCTCOUNT( 'fact'[User])

test.PNG

If this solves your issue then please mark it as the accepted solution. If not then please provide some details about how you would like the data to behave.

View solution in original post

v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

You can create a calculate column using dax below and create a slicer visual based on the calculate column:

Month = MONTH('Table'[Date])

Then create a measure using dax below:

Count = DISTINCTCOUNT('Table'[ID])

Capture.PNG 

 

Community Support Team _ Jimmy Tao

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi guys,

 

it seems that Nskv is right, it seems like I got what I want. I somehow did not see it.

But one more thing came up - is it possible to calculate the number of new IDs for every date (week,month)?

So in my example, when I create a table where in rows will be dates I got

1.1.2019 = 3

1.2.2019 = 1

So I will see that on 1.2.2019 shows 1 new ID (and eg when on 1.3.2019 will show only EFG I got 0)

 

Thanks

Lukas

 

Anonymous
Not applicable

Hi @Anonymous 

 

Would you then please mark it as the solution to make it easier for others to find the answer.

 

Regarding your second question then I would prefer to flag the ID's in the dataset because it could become a very time consuming calculation if you have many different ID's.

v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

You can create a calculate column using dax below and create a slicer visual based on the calculate column:

Month = MONTH('Table'[Date])

Then create a measure using dax below:

Count = DISTINCTCOUNT('Table'[ID])

Capture.PNG 

 

Community Support Team _ Jimmy Tao

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

Anonymous
Not applicable

Hi @Anonymous 

 

I'm not quite sure that I understand your issue correctly, but I manage to get a distinct count on the same data as you. I have created a date table as well which I use as the dimension.

 

distinct users = DISTINCTCOUNT( 'fact'[User])

test.PNG

If this solves your issue then please mark it as the accepted solution. If not then please provide some details about how you would like the data to behave.

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.