Calculate how many people are using a case number to show how many in a house hold.

[inglexjc]

I would like to show in my table how many people are in a house hold.  Is there a way to show this?

Example of how I want the data to show:

Head of Household Name	Client Name	SSN (not real)	Case #	House Hold Size
Jane Doe	Jane Doe	123	1C241	3
Jane Doe	Betty Doe	456	1C241	3
Jane Doe	John Doe	789	1C241	3
No Name Jane	No Name Jane	012	1C574	2
No Name Jane	Husband Jane	345	1C574	2

Since the raw data comes in with each client as their own row it currently shows House Hold Size as 1.

[v-ssriganesh]

Hi @inglexjc,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hello @inglexjc,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I’ve reproduced your scenario in Power BI Desktop using sample data where each individual is listed in their own row along with the same Case #, and I’ve implemented a solution that displays the correct household size (i.e., number of people sharing a Case #) in every row, just as you requested.

I created a calculated column that counts how many individuals share the same Case #, so the household size shows correctly alongside each client name in the table visual.

Here’s the DAX used for the calculated column:

Household Size =

CALCULATE(

    COUNTROWS(HouseholdData),

    ALLEXCEPT(HouseholdData, HouseholdData[Case #])

)

This ensures that each row displays the full household size based on Case # context.

Output:

For your reference, I’m attaching the .pbix file with this setup so you can explore the solution and apply it to your dataset.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[inglexjc]

This is much appreciated.  And really close but what if a client is listed multiple times (and we want them listed multiple times?)  This is an example where the house hold size is 5 but becuase the client is listed more than once they show as house hold size of 34.  I do have SSN or a special ID for each client.

Formula used:

Household Size =

CALCULATE(

    COUNTROWS(V_CBMS_Program_Span),

    ALLEXCEPT(V_CBMS_Program_Span, V_CBMS_Program_Span[CS_ID])

)

[v-ssriganesh · Accepted solution]

Hi @inglexjc,
Thanks for your follow-up and the clarification that’s a great point.

You're right. when the same client is listed multiple times (e.g., same SSN or ID), using COUNTROWS overcounts the household size.

To fix this, you can use DISTINCTCOUNT on the unique client identifier (like SSN or Person ID), so each client is only counted once per household.

Please update your calculated column to:

Household Size =

CALCULATE(

    DISTINCTCOUNT(V_CBMS_Program_Span[SSN]),  -- or PersonID if preferred

    ALLEXCEPT(V_CBMS_Program_Span, V_CBMS_Program_Span[CS_ID])

)

This will ensure the household size reflects unique people per Case ID, even if someone appears multiple times in the dataset.

I trust this addresses your needs. If it does, please “Accept as solution” and give it a "kudos"  to help others find it easily.
Thank you.

[inglexjc]

That was it!  Thank you!

[inglexjc]

Correct.  1c241 (is a sample case id) where a house hold size would show 3.  1c574 (sample case id) where the house hold size would show 2.

[sergej_og]

Better to see the structure of your raw data to get a better idea what to do.
Can you provide a sample of data you'd like act on (pls don't post confidential data)?

[inglexjc]

I provided an example in the table above.  I'm not able to attach the data I'm using becuase it's confidential.

Thank you.

[sergej_og]

OK, hardly readable.
Case = ID of your House hold?
And for 1C241 you'd like to obtain 3 members as a result?

What is your target visualization of these data?
1C241 = 3
1C574 = 2 ?

[sergej_og]

let assume you put "Case" in a table or matrix visual, so you have all your "Case" values as rows reflected.
Then a simple COUNT(table[Client Name]) or COUNT(table[SSN]) should work out.
alternatively you can use COUNTROWS(your table). In that case the filter context will bring you 3 rows for 1C241 etc.

[inglexjc]

When I do this it shows "1" for each row.  Is there not a way for it to show in the table how many are in the house hold?  I do have a visual to show it but would like it to show in the table.

[sergej_og]

this is my result using your sample data:
COUNT and COUNTROWS

Try to adjust the formula to your needs.
I don't know your data model.