Using distinct count in a measure

[arp]

Hi all,

I'm relatively new to the world of Power BI so apologies if this is relatively obvious to some of you...

I have a list of ID numbers that are duplicated within a multi-year dataset. I need to create a series of measures that return distinct counts of these ID numbers for each year and then a related measure showing the variance or % change between different years. I want to display these measures in a multi-row card visualisation.

I've been able to display a distinct count of IDs by year using various chart and matrix visualisations but I'm struggling to produce the measure in DAX that will display the distinc count alongisde the variance/% change in a multi-row card. Here's an example dataset:

ID Number	Year	Group
123	2017	A
333	2018	B
459	2019	C
459	2019	A
333	2018	A
123	2018	B
123	2017	C
333	2018	C
678	2017	C
541	2019	A
541	2018	B
678	2017	A
813	2017	B
888	2019	C
701	2019	C

Any suggestions would be gratefully received!

Thanks

[PattemManohar]

@arp Please try below two as "New Measure"

Test101Count = DISTINCTCOUNT(Test101Measure[IDNumber])

Test101Variance = 
VAR _CurrYear = DISTINCTCOUNT(Test101Measure[IDNumber])
VAR _PrevYear = CALCULATE(DISTINCTCOUNT(Test101Measure[IDNumber]),FILTER(ALL(Test101Measure),Test101Measure[Year]=SELECTEDVALUE(Test101Measure[Year])-1) )
VAR _Var = ((_CurrYear-_PrevYear)/_CurrYear)*100
RETURN _Var

[themistoklis]

How about creating separate measures for each year with both the actual and the % values. 

Measure= CALCULATE(DISTINCTCOUNT('Table'[Values]), 'Table'[year] = 2018)

[arp]

Hi,

Thanks for the response.

This is exaclty what I've tried by I'm getting an error message when I try and use the measure in a visualisation...

[PattemManohar · Accepted solution]

@arpCould you please post the error message that you are getting. Here is the same in "Multi-row card" visual as well

[arp]

Thanks both,

error message no longer displaying, probelm solved!

Thank you very much for your help, much appreciated.