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!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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 Kudoed Authors