Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@arpCould you please post the error message that you are getting. Here is the same in "Multi-row card" visual as well
Proud to be a PBI Community Champion
@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
Proud to be a PBI Community Champion
How about creating separate measures for each year with both the actual and the % values.
Measure= CALCULATE(DISTINCTCOUNT('Table'[Values]), 'Table'[year] = 2018)
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...
@arpCould you please post the error message that you are getting. Here is the same in "Multi-row card" visual as well
Proud to be a PBI Community Champion
Thanks both,
error message no longer displaying, probelm solved!
Thank you very much for your help, much appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |