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.
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:
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).
Solved! Go to Solution.
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]
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]
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |