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.
Hi, this should be an easy one for some of you.
I have a table with the following three fields:
There are a handful of [Person ID] values that show up twice (with different date values where Registration = 1, indicating two registrations for the same person), but I only want to count each person's first registration (i.e., the earliest date within the year) by displaying [Person ID] counts by month. Hope that makes sense.
My current formula is as follows:
DistinctCount_Of_PersonID_Within_Year = CALCULATE(DISTINCTCOUNT(Table[Person ID]),DATESBETWEEN(Table[Date],DATE(2017,08,01),DATE(2018,07,31)))
This results in the correct total count for the year, but the counts for the individual months still sum to more than the total count displayed (so, I know the duplicate [Person ID] values are being counted). It seems that I've just made a long formula for a regular DISTINTCOUNT function. Note that I'm only using the Registration field to filter out non-registrations. I've also tried a DATESINPERIOD function per this thread, but that doesn't seem to work, either.
I'd appreciate any help here! Thanks in advance.
Hi @nfradkin,
You can try to use below measure if it suitable for your requirement:
DistinctCount_Of_PersonID_Within_Year = VAR currDate = MAX ( Table[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Table[Person ID] ), FILTER ( ALLSELECTED ( Table ), YEAR ( Table[Date] ) = YEAR ( CurDate ) && MONTH ( Table[Date] ) IN VALUES ( Table[Date].[MonthNo] ) && Table[Registration ] = 1 ) )
Regards,
Xiaoxin Sheng
Thanks, @v-shex-msft -- I've tried the measure you suggested, but it doesn't seem to be working. I should probably clarify the following:
Thanks again!
HI @nfradkin,
Please provide some sample data with expected result to help us clarify your requirement.
Regards,
Xiaoxin Sheng
Sure, @v-shex-msft -- sample data are below, followed by default summary output and desired summary output. Thanks for your help!
Sample data
Person ID | Date | Registration |
17678 | 1/12/2018 | 1 |
17678 | 1/27/2018 | 0 |
18268 | 8/15/2017 | 1 |
21771 | 3/8/2018 | 1 |
21771 | 3/17/2018 | 0 |
21771 | 3/25/2018 | 0 |
21771 | 5/3/2018 | 0 |
31154 | 6/18/2018 | 1 |
40952 | 8/20/2017 | 0 |
40952 | 9/10/2017 | 0 |
49875 | 9/19/2017 | 1 |
76931 | 8/10/2017 | 1 |
76980 | 5/12/2018 | 1 |
76980 | 6/3/2018 | 0 |
76980 | 7/15/2018 | 0 |
93245 | 10/4/2017 | 1 |
125320 | 12/14/2017 | 1 |
125320 | 12/16/2017 | 0 |
127926 | 11/30/2017 | 1 |
127926 | 1/27/2018 | 0 |
127926 | 2/18/2018 | 1 |
127926 | 2/20/2018 | 0 |
Sum of Registration by Month (default)
Aug | 2 |
Sep | 1 |
Oct | 1 |
Nov | 1 |
Dec | 1 |
Jan | 1 |
Feb | 1 |
Mar | 1 |
Apr | 0 |
May | 1 |
Jun | 1 |
Jul | 0 |
Sum of Registration by Month (desired)
Aug | 2 |
Sep | 1 |
Oct | 1 |
Nov | 1 |
Dec | 1 |
Jan | 1 |
Feb | 0 |
Mar | 1 |
Apr | 0 |
May | 1 |
Jun | 1 |
Jul | 0 |
Hi @nfradkin,
You can try to use below column to replace registration column, I write formula to check first time current id appears and replace with Registration value.
Registration2 = IF ( COUNTROWS ( FILTER ( ALL ( Table ), Table[Person ID] = EARLIER ( Table[Person ID] ) && Table[Date] <= EARLIER ( Table[Date] ) ) ) <= 1, [Registration], 0 )
Regards.
Xiaoxin Sheng
Hi, @v-shex-msft-
I think we're getting close! This seems to work sometimes, but not always. I think the issue might have to do with date values because it seems to work in some instances (though I don't immediately recognize a pattern). Using the replacement column formula you suggested, the Registration2 values for both dates where Registration = 1 were converted to 0. But, again, this wasn't always the case... Any other ideas? Thanks so much for your help.
Hi @nfradkin,
Yes, my formula is based current personal id and date, it will try to find out minimum date of each id and replace with correspond registration value.
For records which not the minimum date, I fully replace them as zero.
Comment:
Highlight part will be replaced with corresponding registration value, remained records' registration value will change to 0
Person ID | Date | Registration |
17678 | 1/12/2018 | 1 |
17678 | 1/27/2018 | 0 |
18268 | 8/15/2017 | 1 |
21771 | 3/8/2018 | 1 |
21771 | 3/17/2018 | 0 |
21771 | 3/25/2018 | 0 |
21771 | 5/3/2018 | 0 |
31154 | 6/18/2018 | 1 |
40952 | 8/20/2017 | 0 |
40952 | 9/10/2017 | 0 |
49875 | 9/19/2017 | 1 |
76931 | 8/10/2017 | 1 |
76980 | 5/12/2018 | 1 |
76980 | 6/3/2018 | 0 |
76980 | 7/15/2018 | 0 |
93245 | 10/4/2017 | 1 |
125320 | 12/14/2017 | 1 |
125320 | 12/16/2017 | 0 |
127926 | 11/30/2017 | 1 |
127926 | 1/27/2018 | 0 |
127926 | 2/18/2018 | 1 |
127926 | 2/20/2018 | 0 |
Hope above can help you clarity my formula logic.
Regards,
Xiaoxin Sheng
Thanks, @v-shex-msft. I think I know what's happening on my end now. What if I instead wanted to use the minimum date after a certain date OR within the last 12 months? Thanks again!!
Hi @nfradkin,
You can add a condition to set up minimum date filter range to 12 month ago of current date:
Registration2 = IF ( COUNTROWS ( FILTER ( ALL ( Table1 ), VAR currDate = EARLIER ( Table1[Date] ) RETURN [Person ID] = EARLIER ( [Person ID] ) && [Date] <= currDate && [Date] >= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) ) ) ) <= 1, [Registration], 0 )
Regards,
Xiaoxin Sheng
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 |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |