Conditional column other table

[Jeremy19]

Hi,

I have two different tables, the first at an individual level and the other at the city level. I need to have for each individual the population in his city for his age group and sex. I think I have to make a conditional column in the first table to get the value. Is this the right solution?

Exemple :

Table A : 

Individual    City   Sex   AgeGroup   

A                  Paris   M       U18

B                  Paris   M        U8

C                  Lille    F         U18

Table B :

City      Pop_M_U8    Pop_M_U18    Pop_F_U8    Pop_F_U18

Paris       1000              2000              1500            3000

Lille          500               700                600             1000

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Zubair_Muhammad · Accepted solution]

@Jeremy19

You might be able to use SWITCH function in DAX

Please see attached file

Assuming above 2 tables are related by "City" Column

Column =
SWITCH (
    TRUE (),
    AND ( [Sex] = "M", [AgeGroup] = "U18" ), CALCULATE ( SUM ( TableB[Pop_M_U18] ) ),
    AND ( [Sex] = "M", [AgeGroup] = "U8" ), CALCULATE ( SUM ( TableB[Pop_M_U8] ) ),
    AND ( [Sex] = "F", [AgeGroup] = "U18" ), CALCULATE ( SUM ( TableB[Pop_F_U18] ) ),
    AND ( [Sex] = "F", [AgeGroup] = "U8" ), CALCULATE ( SUM ( TableB[Pop_F_U8] ) )
)

[v-frfei-msft]

Hi @Jeremy19,

Does that make sense? If so, kindly mark the answer as solution to close the case.

Regards,
Frank