Count distinct values over the period of time (week, month)

[Anonymous]

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

[Anonymous · Accepted solution]

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

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.

[Anonymous]

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]

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 · Accepted solution]

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

 

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 · Accepted solution]

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

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.