Unique Person Count per Year

DAX DAX Calculations
1

Hi All:

I’ve got a issue that I need to solve in Power BI that I thought I’d (hopefully) enlist the forum members help. I’ve got a list of applications received each year (in my sample dataset, 20 per year for 2015-2019) where one person can submit more than one application each year. What I’d like to do is have a measure to return the count of unique persons within each year, so my desired output would be like this:
Unique Person Count per Year

I can solve it in SQL, but cannot get the processed data into Power BI, only the raw data. I’d guess I’m missing something small, but haven’t seen the way forward yet. Any thoughts?

Thanks,
Greg
Unique Person Count per Year.xlsx (11.8 KB)
Unique Person Count per Year.pbix (18.4 KB)

2

Hi @Greg,

Hmm, you missed very simple thing I believe and it happens :slight_smile: . Are you looking for below:

Unique Persons = DISTINCTCOUNT(‘Applications’[Person])

image

1 Like
3

@Greg,

Does this get you what you need?:

 Count Apps = 
CALCULATE(
    COUNTROWS( Data ),
    ALLEXCEPT( 
        Data,
        Data[Year]
    )
)

Unique Persons = 
CALCULATE(
    DISTINCTCOUNTNOBLANK( Data[Person] ),
    ALLEXCEPT(
        Data,
        Data[Year]
    )
)

image

1 Like
4

Thanks both … I don’t think distinctcount is available for my clients installation, which is (gasp) the April 2019 version of Power BI Desktop …hopefully I’m wrong … I’ll check tomorrow.

Thanks for your help.
Greg

5

@Greg,

Oh, this is one of those “the client wants a cake, but we’re not allowed to use flour or sugar, and they’re allergic to chocolate” scenarios…

Recipe #2:

 Unique Persons vTable = 

VAR vTable =
SUMMARIZE(
    Data,
    Data[Year],
    Data[Person]
)

VAR UniquePers =
CALCULATE(
    COUNTROWS( vTable ),
    ALLEXCEPT(
        Data,
        Data[Year]
    )
)

RETURN
UniquePers

image

  • Brian

eDNA Forum – Unique Persons Solution.pbix (18.5 KB)

3 Likes
6

Thanks @BrianJ … this works in the April 2019 release … thanks for your patience. I only know how to cook with my credit card (outside of steak, eggs, spaghetti, and cereal), so seeing the problem through the eyes on another “chef” is greatly appreciated. Thanks, Greg

7

@Greg,

Cool - glad that was helpful. I like these kind of problems, since they force you to take a different route than you otherwise would.

  • Brian