Forum Discussion
Modelling with multiple fact tables at different aggregations
dim_industry contains one value perstate, dim_state contains one value per salesperson (and multiple for state), dim_salesperson contains one value per salesperson (and multiple for customers).
The above info that you have shared is a bit unclear to me, I believe you might have a dim_state dimension table which contains one unique record (row) representing each state. If yes then you should ideally create a relation from this table to fact_one and fact_two to build the insight that you want to create i.e "Sate wise actual and Projected Sales".
Since that is not the case, I would like to suggest you an option which will avoid the ambigous relation path error
1. Create the relationship between dim_state and fact_one, this willl get you the "State wise actual sales"
2. Instead of creating a relationship from dim_state to fact (which is not possible), create a meausre in this way
ProjectedSales = Calculate(Sum(fact_two[Projected sales]), Treatas(Values(dim_state[sales Person]), fact_two[sales person]))treatas can pass the sales person values of each state to fact_two and can retrieve the projected sales values corresponds to them.
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
Although there are multiple fact tables in your model, as long as you are creating 'Single direction one to many' relationships from dim to fact, the chances of ambigous paths are rare.
1. Make sure the Unique key columns in the dimension tables do not have duplicates.
2. Make sure there are no RI violations.
3. As you have multiple fact tables, depending on the analysis you want to perform this data, you would need to aggregate columns in fact table using the columns in dimension table. Accordingly create the relationships in your model.
4. If you are building any insight where you want to show the metrics taken from two different fact tables, then the dim column that you are using to perform the aggregation should come from a table from which there is an active single direction one to many relationship to fact table, otherwise you will end up with wrong insights.
For further help I would suggest you share more information about the insight that you want to build and complete schema of your model. If possibke share the pbix file after removing sensitive and confidential info
Connect on LinkedIn
|
Unfortuantely impossible to share the pbix file.
>
4. If you are building any insight where you want to show the metrics taken from two different fact tables, then the dim column that you are using to perform the aggregation should come from a table from which there is an active single direction one to many relationship to fact table, otherwise you will end up with wrong insights.
For example, if I wanted to report by state projected sales and actual sales, which dim table would I get the state column from?
I can join fact_two to dim_state and fact_one to dim_channel, but this won't allow me to compare projected and actuals in the same table.
If I make a staging table between dim_state and dim_channel (consistenting of just unique state values) and set direction to bidirectional, I run into ambiguous path errors.
dim_industry contains one value perstate, dim_state contains one value per salesperson (and multiple for state), dim_salesperson contains one value per salesperson (and multiple for customers).
The above info that you have shared is a bit unclear to me, I believe you might have a dim_state dimension table which contains one unique record (row) representing each state. If yes then you should ideally create a relation from this table to fact_one and fact_two to build the insight that you want to create i.e "Sate wise actual and Projected Sales".
Since that is not the case, I would like to suggest you an option which will avoid the ambigous relation path error
1. Create the relationship between dim_state and fact_one, this willl get you the "State wise actual sales"
2. Instead of creating a relationship from dim_state to fact (which is not possible), create a meausre in this way
ProjectedSales = Calculate(Sum(fact_two[Projected sales]), Treatas(Values(dim_state[sales Person]), fact_two[sales person]))treatas can pass the sales person values of each state to fact_two and can retrieve the projected sales values corresponds to them.
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
