Distinctcount from table A related to id from table B

[AFra]

Hi, 

I have two tables : 

- table A contains ID for each family, ID for each person, and birthdates

- table B contains ID for each family 

The two tables have a many (table A) to one (table B) relationship based on ID for each family 

I need to create a column in table B with the number of persons by family, so I created : 

NBR_OCCUPANTS =

var nombre = CALCULATE(

                            DISTINCTCOUNT(A2[IDENTIFIANT_LOCATAIRE]),

                            GROUPBY(A1, A1[IDENTIFIANT_MENAGE]))

return

if(nombre<9, CONCATENATE(nombre, " pers."), "9 et +")

it works nicely, but then I need also a column with all the children by family. I tried to apply the same logic with an extra filter on birthdate, but it gives me an error : "Une dépendance circulaire a été détectée : A1[Colonne], A1[NBR_OCCUPANTS], A1[Colonne]." 

Can someone help me please? 

Here's a sample of the data 

[MFelix]

Hi @AFra ,

The connection is not working properly can you please share the link again.

[AFra]

Hi @MFelix , 

here's the link : https://we.tl/t-HgmzDfTeyV

[MFelix]

Hi @AFra ,

Try adding the following columns in dax:

NBR_OCCUPANTS = 
var nombre = CALCULATE(COUNTROWS(TABLEA))
Return
if(nombre < 9, CONCATENATE(nombre, " pers."), "9 et +")

NBR_OCCUPANTS_enfant = 
var nombre = CALCULATE(
    COUNTROWS(
        FILTER(ADDCOLUMNS(TABLEA,"Age", DATEDIFF(TABLEA[DATE_DE_NAISSANCE], TODAY(), YEAR)), [Age] <18)
        )
    ) + 0
Return
if(nombre < 9, CONCATENATE(nombre, " enfants"), "9 et +")

No error for circularity will be returned.