Help with DAX measure - Unique Members within Bins

[jasonbakersd]

I'm having trouble creating a measure that will show total unique members by Bins based on the sum of thier individual fact rows.

The table on the right shows is what I'm trying to create with DAX as opposed to single purpose reporting table.

The fact table has the number of prescription drug refills by member.   

If member 123 has 3 refills of a drug, he or she will fall into the 3 bin.   I only count member 123 once.

Any help greatly appreciated. 

Jason

[v-sihou-msft · Accepted solution]

@jasonbakersd

In your scenario, I think you can add a calculated column for "refill times" in your fact table. 

Refill Times = CALCULATE(COUNTROWS(),ALLEXCEPT('Fact','Fact'[Drug],'Fact'[Member]))

Then create a measure to distinct count members group on Drug and Refill Times.

Distinct Members = CALCULATE(DISTINCTCOUNT('Fact'[Member]),ALLEXCEPT('Fact','Fact'[Drug],'Fact'[Refill Times]))

Regards,

[jasonbakersd]

Simon, 

I've implemented this and it works great.   My only change was to add formatting to the Refill column to place any count 5 or greater into a 5+ bucket.

Thanks again for the help!

[jasonbakersd]

Thanks, I'll take a crack using your suggestions.