Forum Discussion

baronraghu's avatar
baronraghu
Helper III
2 years ago

Filter two tables

HI All,

 

I have Three Tables 

Table 1

State

Uttar Pradesh
Punjab
Tamil Nadu
Kerala

 

Table 2  
StateCityPopulation
Uttar PradeshLucknow170248
PunjabMohali467409
Tamil NaduChennai238767
KeralaKottayam440756
Uttar PradeshKanpur131319
PunjabAmritsar389309
Tamil NaduVellore158096
KeralaCochin182864

 

Table 3  
StateCityCustomers
Uttar PradeshLucknow49144
PunjabMohali38368
Tamil NaduChennai23973
KeralaKottayam38123
Uttar PradeshKanpur13518
PunjabAmritsar14859
Tamil NaduVellore42129
KeralaCochin36744

 

RElationship between Table 1 and Table 2 is through States

Relationship Between Table 1 and Table 3 is aslo through States

 

This is the output I am seeking

CityPopulationCustomers
Lucknow17024849144
Mohali46740938368
Chennai23876723973
Kottayam44075638123
Kanpur13131913518
Amritsar38930914859
Vellore15809642129
Cochin18286436744

 

I tried Using crossfilter but not getting a correct answer. Request your help

Tips and Tricks

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi baronraghu ,

     

    you need to create another dimension table with the cities with a one to many relationship between that table and the other two table then you can use it to create the expected visual.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the reply from MFelix , please allow me to provide another insight: 

    Hi  baronraghu ,

     

    Here are the steps you can follow：

    Create calculated table.

    Table =
SUMMARIZE(
    'Table2',[City],[Population],
    "Customers",
    SUMX(FILTER(ALL('Table3'),
    'Table3'[City]=EARLIER('Table2'[City])),[Customers]))

    You might also consider creating measure：

    Measure =
SUMX(
    FILTER(ALL('Table3'),
    'Table3'[City]=MAX('Table2'[City])),[Customers])

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly