Counting duplicat values

[leibowjb]

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:

1. Unique: The unique # of vins that came into the store which I can obtain by using distinctcount
2. 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).

[v-sihou-msft · Accepted solution]

@leibowjb

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,

[v-sihou-msft · Accepted solution]

@leibowjb

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,

[leibowjb]

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))

[pnvinod · Accepted solution]

In the CALCULATE  you can add another FILTER => ALLSELECTED([Dates]