Modelling with multiple fact tables at different aggregations

[chris_cjg]

I'm refininig a rather complex model but I'm running into issues with bidirectional filtering trying to model the below.

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). 

However, fact tables come in a few different ways - some fact tables are done by salesperson, some by customer, and some fact tables are done by state.

There's no way to change how the fact tables are delivered.

How best to model this and avoid running into the "relationships are ambigious" issue with bidirectional filtering?

[v-lgarikapat]

Hi @chris_cjg ,

I wanted to follow up and confirm whether you’ve had the opportunity to review the information provided by @Shahid12523 @tharunkumarRTK @Diksha_2 .If you have any questions or need further clarification, please don’t hesitate to reach out.

We appreciate your collaboration and support!

Best regards,
Lakshmi.

[v-lgarikapat]

Hi @chris_cjg ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[v-lgarikapat]

Hi @chris_cjg ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,
Lakshmi.

[Shahid12523]

Keep facts at their natural grain (state vs salesperson).

Use single-direction relationships only (no bi-directional).

Add bridge tables (e.g., State–Salesperson) if you need to connect grains.

Use DAX (TREATAS, USERELATIONSHIP) to cross grains in measures.

Never force all facts into one path — handle cross-grain logic explicitly.

[Diksha_2]

Whenever I face this situation, I avoid making an active relationship. Instead I make inactive relation and use them in DAX based on my requirement.

[tharunkumarRTK]

@chris_cjg 

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

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!

[chris_cjg]

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.

[tharunkumarRTK · Accepted solution]

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!