Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Get DistinctCount of ColumnB based on ColumnA

Need help to get DistinctCount of ColumnB (ServiceVisitYr) based on ColumnA (ServiceAddress)

 

ServiceAddressServiceVisitYr
A12017
A12017
A12018
A12019
A12019
A22017
A22019
A32017
A32017
A32019
A42019
A42019
A52018

 

 

Expected Result

 

ServiceAddressServiceVisitYrDistinctYrs
A120173
A120173
A120183
A120193
A120193
A220172
A220192
A320172
A320172
A320192
A420191
A420191
A52018

1

 

 

Thanks for your help in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago
    Measure = CALCULATE(DISTINCTCOUNT('Table'[ServiceVisitYr]),ALLSELECTED('Table'[ServiceVisitYr]))

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    CALCULATE(DISTINCTCOUNT(Test[ServiceVisitYr]),ALLEXCEPT(Test,Test[ServiceAddress]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your answer.

       

      Your solution works in the data set presented above.

       

      The other problem is I'm using slicer on 

      ServiceVisitYr
      and when I filter data I do not get correct count
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Measure = CALCULATE(DISTINCTCOUNT('Table'[ServiceVisitYr]),ALLSELECTED('Table'[ServiceVisitYr]))