Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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 ,
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.
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
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.
@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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
109 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |