Forum Discussion

arp's avatar
arp
Regular Visitor
7 years ago
Solved

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

Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

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

     

5 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion
    7 years ago

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

    Measure= CALCULATE(DISTINCTCOUNT('Table'[Values]), 'Table'[year] = 2018)
    • arp's avatar
      arp
      Regular Visitor
      7 years ago

      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's avatar
        PattemManohar
        Community Champion
        7 years ago

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

         

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    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