Combine Two Source Tables with Different Relationships in dimension table columns
- Thread starter dilawer5
- Start date
dilawer5
Member
Dear Dilawer!
First you need to understand that both source tables should be linked with all tables.
1. For this you first merge Customer table & Distributor table by adding helper column (Customer name& Distributor Name)
2. Secondly you need to add helper columns in both sources which is (item code& item name.
Now data is shaped better. While merge two source table in power query then add helper column which has filter primary and secondary sales.
Hope you understand.
First you need to understand that both source tables should be linked with all tables.
1. For this you first merge Customer table & Distributor table by adding helper column (Customer name& Distributor Name)
2. Secondly you need to add helper columns in both sources which is (item code& item name.
Now data is shaped better. While merge two source table in power query then add helper column which has filter primary and secondary sales.
Hope you understand.
Chihiro
Excel Ninja
... are you having conversation with yourself?? 
At any rate, for ease of reporting and future maintenance you should design star schema in data model.
You can read more on it in link. Specifically, "Adjusting cross filter direction for a complex set of tables of relationships" section.
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/
At any rate, for ease of reporting and future maintenance you should design star schema in data model.
You can read more on it in link. Specifically, "Adjusting cross filter direction for a complex set of tables of relationships" section.
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/
Chihiro
Excel Ninja
Here's detailed how to on creating star schema model.
https://businessintelligist.com/201...n-power-bi-and-power-pivot-using-power-query/
https://businessintelligist.com/201...n-power-bi-and-power-pivot-using-power-query/
@dilawer5
First up, stop talking to yourself in public. :D
Jokes aside, I think a better option is to combine two tables to one using either Power Query or Combine DAX function. You may also add a type column in source tables to distinguish tables. Then set up star schema (ie just connect the dimension tables to single fact) and use CALCULATE to filter your measures by either table values.
If you wish to retain both tables, you can use the excellent USERELATIONSHIP() to define which relationship should be used when defining the filter context for the measure.
Hope that helps.
First up, stop talking to yourself in public. :D
Jokes aside, I think a better option is to combine two tables to one using either Power Query or Combine DAX function. You may also add a type column in source tables to distinguish tables. Then set up star schema (ie just connect the dimension tables to single fact) and use CALCULATE to filter your measures by either table values.
If you wish to retain both tables, you can use the excellent USERELATIONSHIP() to define which relationship should be used when defining the filter context for the measure.
Hope that helps.