Dax Calculation

[rm.gohain]

Hi all
I have this table with Participants with ID. Some Participants also belong to a Family with ID (4095)
The yellow rows are head of households = All participants belong to that family

What I need to calculate =

1. How many people we have supported who has a need Domain = Housing
   Answer Expected = 5 ( Participant ID 31379 is head of household ) = 31379, 34494, 34495, 34496 + 34497 ( Individual )
2. How many people we have supported who has a need Domain = Child Protection
   Answer Expected = 4 ( Participant ID 31379 is head of household ) = 31379, 34494, 34495, 34496

Thank you in advance, I have attached a table with a sample set of data

Query Table.xlsx (14.7 KB)

[Melissa]

Hi @rm.gohain,

Welcome to the Forum!

To simplify the DAX needed to answer your question, I added a # Family members column to your table.
Here are the Power Query steps:

1. Added a FileLocation Parameter to the PBIX file,
   If you update this to the file location on your system the query will be restored.
2. Loaded your PD_ETO_TEST_v3 table
3. Added a TableBuffer step containing the distinct Participant ID and Family ID
   
   

Next added the # Family members column

Now all you need to do in DAX is a SUM on the # Family members column for distinct Participant IDs.

People supported = 
SUMX(
    GROUPBY( PD_ETO_TEST_v3, PD_ETO_TEST_v3[Participant ID], 
        "Number", MAXX(CURRENTGROUP(), [# Family members])), 
    [Number]
)

And here is the result:

For more on GROUPBY please see:
https://forum.enterprisedna.co/t/how-to-use-the-groupby-function-in-power-bi-dax-function-review/4060

Here are the files, I hope this is helpful.
Query Table.xlsx (14.7 KB)
eDNA Forum Query Table.pbix (39.7 KB)