Total Count by ID and Another Column

[TaylorLile]

Hello,

I have the following table: 

ID	Diagnosis	Source	Status
1	A	Claims	Eligible
1	B	Medcation	Eligible
1	C	NULL	Not Eligible
2	A	NULL	Not Eligible
2	B	Claims	Eligible
2	C	NULL	Not Eligible
3	A	Medcation	Eligible
3	B	NULL	Not Eligible
3	C	Claims	Eligible

I'm trying to add a Total Eligibilities by ID Column (Note: A Source of NULL means they are not Eligible) like so:

ID	Diagnosis	Source	Status	Total Eligibilities
1	A	Claims	Eligible	2
1	B	Medcation	Eligible	2
1	C	NULL	Not Eligible	2
2	A	NULL	Not Eligible	1
2	B	Claims	Eligible	1
2	C	NULL	Not Eligible	1
3	A	Medcation	Eligible	2
3	B	NULL	Not Eligible	2
3	C	Claims	Eligible	2

I have tried the following with no success: 

Total Eligibilities = CALCULATE(DISTINCTCOUNT('Patient Conditions'[Diagnosis]),ALLEXCEPT('Patient Conditions','Patient Conditions'[Status]),ALLEXCEPT('Patient Conditions','Patient Conditions'[ID]))

I'm obviously missing something, but am not sure what.

Any help would be appreciated. Thanks!

[amitchandak]

@TaylorLile 

Try like

Total Eligibilities = CALCULATE(DISTINCTCOUNT('Patient Conditions'[Diagnosis]),ALLEXCEPT('Patient Conditions','Patient Conditions'[Status]),'Patient Conditions'[Status]= "Eligible")

Also, this gives a good example to deal with subtotal :https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[TaylorLile]

Thanks, for the response @amitchandak. That gets me closer, but it is now only giving me the distinct number of diagnosis with a status of "Eligible" for the entire dataset, not by ID. I tried added another ALLEXCEPT function including the ID, but that didn't change anything. 

Thanks for the link, I'll look at that now as well. 

Appreciate the help!

-Taylor

[amitchandak · Accepted solution]

@TaylorLile 

Make sure you are using do not summarize on Id column when you put to the table

Measure = CALCULATE(COUNTROWS(FILTER(Sheet1,Sheet1[Status]="Eligible")),ALLEXCEPT(Sheet1,Sheet1[ID]))

Pbix is attached after signature.

if you need more help make me @

Appreciate your Kudos.