Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I am trying to do a distinct count of people based on date ending period. So, if a person in Region X under Project A is counted once for a date period, then he/she need not be counted again for any other region and project as well. This is characterized by Date field available. Below screenshot is from the PowerQuery setup:
As you can see from above, I merged the Date and the username to generate a unique Index ID. I plotted a matrix chart to check and below is something I am getting:
If you take a look, on 1/31/2021 - Danny is distinct counted multiple times in projects: ABC, ZKY and GHI. We want Danny to be counted only once for that date period 1/31/2021. Currently I am doing Distinct count of the INDEX column. Please suggest any solution for the same.
Any help is appreciated.
Regards!
Solved! Go to Solution.
Hi @Sashwato ,
You can add an index column like
The Merged column is created by dax
Merged = [Date]&"-"&[Username]
Then I create the following rank column
Rank = RANKX(FILTER('Table',[Merged]=EARLIER('Table'[Merged])),[Index],,ASC,Dense)
Finally create a measure
Measure = IF(MAX('Table'[Rank])=1,1)
You can click Show items with no data to display empty values.
Check more details from the attachment
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Sashwato ,
You can add an index column like
The Merged column is created by dax
Merged = [Date]&"-"&[Username]
Then I create the following rank column
Rank = RANKX(FILTER('Table',[Merged]=EARLIER('Table'[Merged])),[Index],,ASC,Dense)
Finally create a measure
Measure = IF(MAX('Table'[Rank])=1,1)
You can click Show items with no data to display empty values.
Check more details from the attachment
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Sashwato , In the project view it will be counted or shown multiple times. But as you can see on grand total it counted once. That is correct.
Yes @amitchandak true. I agree, however our management would like to see the count against the name being shown once once rather than 3 times in other projects since they would like to track it on individual basis.
Is there a measure or something that helps with it?
Regards!
@Sashwato , Try a measure like
measure =
var _1 = calculate(min(Table[project]), filter(allselected(Table) ,Table[project] = min(Table[project] ))
return
if( not(isblank(distinctCOUNT(Table[Index]))) && max(Table[project]) =_1 , distinctCOUNT(Table[Index]), blank())
Thank you @amitchandak
I tried using the measure to my context as below:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |