Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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,
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,
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!
Thanks, I'll take a crack using your suggestions.