Forum Discussion
Counting duplicat values
I have a dataset related to automotive service data. The dataset has 165K rows which is the total number of repair orders that came into the shop.
I need to calculate two measures:
- Unique: The unique # of vins that came into the store which I can obtain by using distinctcount
- 2+ Visits: The count of vins that came in 2+ times
If I have the above two counts, I can then divide the ‘2+ visits’ number by ‘unique’ to obtain the % of unique vins that have come into the shop more than once.
I am having trouble figuring out a dax formula for #2 above – essentially, counting values where the value shows up more than once (is duplicated).
In this scenario, you can create a variable to get th count of visits for each user.
=CALCULATE(COUNTA(Table[VisitDate]),ALLEXCEPT(Table,Table[User]))
Then you can create a measure to filter users with more than 2 visits.
2 plus visits users = VAR CountOfVisits = CALCULATE ( COUNTA ( Table[VisitDate] ), ALLEXCEPT ( Table, Table[User] ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Table[User] ), FILTER ( Table, CountOfVisits >= 2 ) )Regards,
In the CALCULATE you can add another FILTER => ALLSELECTED([Dates]
3 Replies
In this scenario, you can create a variable to get th count of visits for each user.
=CALCULATE(COUNTA(Table[VisitDate]),ALLEXCEPT(Table,Table[User]))
Then you can create a measure to filter users with more than 2 visits.
2 plus visits users = VAR CountOfVisits = CALCULATE ( COUNTA ( Table[VisitDate] ), ALLEXCEPT ( Table, Table[User] ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Table[User] ), FILTER ( Table, CountOfVisits >= 2 ) )Regards,
Thank you so much! This works except for when I put a slicer on my dashboard and filter for a specific date range. Any suggestions? Essentially, when I filter for certain open date ranges, I want my formulas to recalculate based on my specified date range. Thanks again for all the help.
CountofVisits = CALCULATE(COUNTA(Table[Open Date]),ALLEXCEPT(Table,Table[VIN]))
Count2+ = CALCULATE(DISTINCTCOUNT(Table[VIN]),FILTER(Table,[CountofVisits]>=2))
In the CALCULATE you can add another FILTER => ALLSELECTED([Dates]
