Filter two tables

[baronraghu]

HI All,

I have Three Tables 

Table 1

State

Uttar Pradesh

Punjab

Tamil Nadu

Kerala

Table 2
State	City	Population
Uttar Pradesh	Lucknow	170248
Punjab	Mohali	467409
Tamil Nadu	Chennai	238767
Kerala	Kottayam	440756
Uttar Pradesh	Kanpur	131319
Punjab	Amritsar	389309
Tamil Nadu	Vellore	158096
Kerala	Cochin	182864

Table 3
State	City	Customers
Uttar Pradesh	Lucknow	49144
Punjab	Mohali	38368
Tamil Nadu	Chennai	23973
Kerala	Kottayam	38123
Uttar Pradesh	Kanpur	13518
Punjab	Amritsar	14859
Tamil Nadu	Vellore	42129
Kerala	Cochin	36744

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

City	Population	Customers
Lucknow	170248	49144
Mohali	467409	38368
Chennai	238767	23973
Kottayam	440756	38123
Kanpur	131319	13518
Amritsar	389309	14859
Vellore	158096	42129
Cochin	182864	36744

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

[Anonymous]

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

[baronraghu]

I think my query got created twice.

This is the original request here

https://community.fabric.microsoft.com/t5/Desktop/Cross-Filter-Two-Tables/m-p/3999126

[MFelix]

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.