Dax Calculation

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)

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:
Query%20Table%20Result

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)

2 Likes