Forum Discussion
Join two filtered tables
Hi All,
I have two tables
Table 1
| Buyer | City | Quantity Bought |
| Adam | LA | 17 |
| BoB | London | 80 |
| Charle | Moscow | 37 |
| Diaz | LA | 28 |
| Ellen | LA | 82 |
| Alok | Delhi | 52 |
| Ankit | London | 51 |
| Adam | Delhi | 73 |
| BoB | Delhi | 86 |
| Charle | London | 20 |
| Diaz | LA | 30 |
| Ellen | LA | 50 |
| Alok | Delhi | 78 |
| Ankit | Delhi | 85 |
Table 2
| City | Type | Houses | Shops | GDP |
| LA | A | 317 | 89 | 6 |
| LA | B | 298 | 165 | 3 |
| LA | C | 183 | 168 | 5 |
| LA | D | 413 | 101 | 6 |
| London | A | 260 | 173 | 10 |
| London | B | 233 | 101 | 7 |
| London | C | 227 | 189 | 1 |
| London | D | 152 | 109 | 4 |
| Delhi | A | 470 | 77 | 4 |
| Delhi | B | 437 | 151 | 4 |
| Delhi | C | 378 | 68 | 2 |
| Delhi | D | 164 | 130 | 3 |
I need to merge certain rows of table 1 with that of table 2 based on slicer selection, like below
I have a hierchey Slicer with following levels Buyers> City > Type
So if my selections are (Adam, LA, B) on all the three slicers, I want to create a table to be made with folowing headers
Buyer | City | Type | HOuses| Shops | GDP
I tried Union function like this
New table= union(if(isfiltered(all(table1[buyers]),table1[buyers],blank()),if(isfiltered(table2[type]),Filter(table2,Allexcept(table2,table2[type]))
The formula doesnt seem to work properly.
Please help
Thanks
Raghu
3 Replies
Hi baronraghu
You need to join both the tables either in DAX or in Power Query.
In Power Query :
Do Merge both tables and retain required columns only. Once slicers filters are applied, your table will be automattically applied here as well. Pls make sure proper relationship is defined.
In DAX:
In Modelling -> New Table-> You may have to use NATURALINNERJOIN
Hope this gives you the direction.
Thanks
Raj
Thanks Anonymous
I tried using the naturalinnerjoin function, but it didnt work :(
