Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Need help to get DistinctCount of ColumnB (ServiceVisitYr) based on ColumnA (ServiceAddress)
ServiceAddress | ServiceVisitYr |
A1 | 2017 |
A1 | 2017 |
A1 | 2018 |
A1 | 2019 |
A1 | 2019 |
A2 | 2017 |
A2 | 2019 |
A3 | 2017 |
A3 | 2017 |
A3 | 2019 |
A4 | 2019 |
A4 | 2019 |
A5 | 2018 |
Expected Result
ServiceAddress | ServiceVisitYr | DistinctYrs |
A1 | 2017 | 3 |
A1 | 2017 | 3 |
A1 | 2018 | 3 |
A1 | 2019 | 3 |
A1 | 2019 | 3 |
A2 | 2017 | 2 |
A2 | 2019 | 2 |
A3 | 2017 | 2 |
A3 | 2017 | 2 |
A3 | 2019 | 2 |
A4 | 2019 | 1 |
A4 | 2019 | 1 |
A5 | 2018 | 1 |
Thanks for your help in advance.
HI @Anonymous
You can create a measure like.
Measure = CALCULATE( DISTINCTCOUNT( 'Table'[ServiceVisitYr] ), ALL( 'Table'[ServiceVisitYr] ) )
or column like this.
Column = CALCULATE( DISTINCTCOUNT( 'Table'[ServiceVisitYr] ), ALL( 'Table'[ServiceVisitYr] ) )
Thanks for your answer.
Your solution works in the data set presented above.
but, I have other columns in my table and to make it work I used
CALCULATE(DISTINCTCOUNT('Table'[ServiceVisitYr]), ALLEXCEPT('Table','Table'[ServiceAddress]))
The other problem is I'm using slicer on
ServiceVisitYr
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |