cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Using distinct count in a measure

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 NumberYearGroup
1232017A
3332018B
4592019C
4592019A
3332018A
1232018B
1232017C
3332018C
6782017C
5412019A
5412018B
6782017A
8132017B
8882019C
7012019C

 

Any suggestions would be gratefully received!

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Using distinct count in a measure

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

 

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Highlighted
Memorable Member
Memorable Member

Re: Using distinct count in a measure

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

Measure= CALCULATE(DISTINCTCOUNT('Table'[Values]), 'Table'[year] = 2018)
Highlighted
Super User I
Super User I

Re: Using distinct count in a measure

@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

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Regular Visitor

Re: Using distinct count in a measure

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

Highlighted
Super User I
Super User I

Re: Using distinct count in a measure

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

 

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Regular Visitor

Re: Using distinct count in a measure

Thanks both,

 

error message no longer displaying, probelm solved!

 

Thank you very much for your help, much appreciated.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors