Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jeremy19
Helper III
Helper III

Conditional column other table

 

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

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@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] ) )
)

cities.png

 


Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Zubair_Muhammad
Community Champion
Community Champion

@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] ) )
)

cities.png

 


Regards
Zubair

Please try my custom visuals

Hi @Jeremy19,

 

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


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.