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

[Anonymous]

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.

[Mariusz]

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

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

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

and when I filter data I do not get correct count