Forum Discussion
Using slicer to get other table information
Can anyone teach me how to use slicer feature that get from current table to get the information from other table? I required when I choose the Customer that from Table 1 in slicer, the Total Item Amount from relevant customer will shown up. I already created many to many relationship between the Customer of both table.
Table 1 data column : Customer ; Purchase Amount
Table 2 data column : Customer ; Item Amount
Thank you for advance.
Hay Alferd2727
You want to filter "Purchase Amount" and"Item Amount" with the help of "Customer" and join these two tables with many to many relationships.
This idea is not appropriate. There will be some problems, for example,
- Few customer could not be available in the both tables
- Same customer could be available multiple times
So, the best solution is go to power query editor and do these steps.
- append both tables and its out fact tables, follow this link to do append queries.
- Create a duplicated copy of the queries and keep only customer column, finally rename it dim_customer.
- Remove all duplicated records from dim_customer.
- Now, save and close power query editor and go to model view to join dim_customer[Customer] with fact[Customer]. Relationship type is one to many.
- Now, you can use Customer from dim_customer table to get the best result.
Remember, power bi works best with one to many or many to one and filter data will flow from the direction arrow, in this case our join type is single so, data will always flow from dim_customer to fact table.
If i answer your questions, please accept it.
Thanks
1 Reply
Hay Alferd2727
You want to filter "Purchase Amount" and"Item Amount" with the help of "Customer" and join these two tables with many to many relationships.
This idea is not appropriate. There will be some problems, for example,
- Few customer could not be available in the both tables
- Same customer could be available multiple times
So, the best solution is go to power query editor and do these steps.
- append both tables and its out fact tables, follow this link to do append queries.
- Create a duplicated copy of the queries and keep only customer column, finally rename it dim_customer.
- Remove all duplicated records from dim_customer.
- Now, save and close power query editor and go to model view to join dim_customer[Customer] with fact[Customer]. Relationship type is one to many.
- Now, you can use Customer from dim_customer table to get the best result.
Remember, power bi works best with one to many or many to one and filter data will flow from the direction arrow, in this case our join type is single so, data will always flow from dim_customer to fact table.
If i answer your questions, please accept it.
Thanks