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,
Suppose I have a list of people, along with the dates that they accessed a venue, as below.
Person | Date of Access |
Alice | 1/01/2022 |
Alice | 2/01/2022 |
Alice | 10/01/2022 |
Bob | 4/01/2022 |
Carol | 8/01/2022 |
Dave | 6/01/2022 |
Eva | 4/01/2022 |
Eva | 8/01/2022 |
Eva | 9/01/2022 |
Eva | 10/01/2022 |
Eva | 11/01/2022 |
Eva | 12/01/2022 |
Eva | 22/01/2022 |
I'd like to count the number of people who accessed the venue more than once. Based on the data above, the answer should be 2 (Alice and Eva).
How do you make this calculation using DAX?
Thanks!
Solved! Go to Solution.
@Anonymous there are probably more consise ways to accomplish, but I find it helpful to do such calculations in steps so that so that I can better visualize what's happening.
Here's one way:
Count Duplicates =
VAR _CountbyPerson =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Person] ),
"@Count", CALCULATE ( DISTINCTCOUNT ( 'Table'[Date of Access] ) )
)
VAR _GreaterThan1 =
FILTER ( _CountbyPerson, [@Count] > 1 )
VAR _Result =
COUNTROWS ( _GreaterThan1 )
RETURN
_Result
@Anonymous there are probably more consise ways to accomplish, but I find it helpful to do such calculations in steps so that so that I can better visualize what's happening.
Here's one way:
Count Duplicates =
VAR _CountbyPerson =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Person] ),
"@Count", CALCULATE ( DISTINCTCOUNT ( 'Table'[Date of Access] ) )
)
VAR _GreaterThan1 =
FILTER ( _CountbyPerson, [@Count] > 1 )
VAR _Result =
COUNTROWS ( _GreaterThan1 )
RETURN
_Result
Thank you very much, @ebeery. The way you laid it out makes it easy for novices like me to follow your logic too!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |