Creating Relationships with duplicative values

[PhilSap]

Hey

This is the issue I am running into

I have 2 tables

One has Data from the US & CA related to sales but another table has site session data related to CA & US

I can get the data to work for a relationship that is basically Linked by Country between the tables. When I add april data the relationship no longer works. Any ideas?

[ABD128 · Accepted solution]

Hi @PhilSap 

The issue likely stems from bidirectional filtering in your many-to-one relationship. When relationships are set to filter in both directions, especially in a many-to-one setup, it can cause context propagation issues and inflated aggregations, leading to incorrect calculations like the 27.17 ratio you're seeing. Even though you created a composite key, if the relationship is still ambiguous or if the data model allows both tables to filter each other, Power BI might be duplicating values during evaluation.

Use TREATAS to control context explicitly: Instead of relying on automatic relationship filtering, use TREATAS to apply the correct filter context in your measure.

If you are still facing the issue, it is advisable to reach out to Microsoft Support for better assistance

You can submit a ticket through the Microsoft Power BI Support Portal:

https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket.

Thanks 

[ABD128 · Accepted solution]

Hi @PhilSap 

Power BI can accurately calculate total values for cross-table ratios, but only when relationships, filter directions, and context are modeled correctly. In scenarios with multiple fact tables or ambiguous filter paths, totals may appear incorrect not due to a system limitation, but due to the underlying data model. If the total still seems off, it's a signal to revisit the model structure. Common fixes include introducing a proper date or country-month dimension table, adjusting relationship directions, or using revised DAX with clearer filter context. With the right design, totals and row-level results will align consistently. It's better to reach out to Microsoft Support for Better Assistance.

If this post helps, kindly mark it as Accepted Solution.

Thank You.

[ABD128 · Accepted solution]

Hi @PhilSap 

The issue likely stems from bidirectional filtering in your many-to-one relationship. When relationships are set to filter in both directions, especially in a many-to-one setup, it can cause context propagation issues and inflated aggregations, leading to incorrect calculations like the 27.17 ratio you're seeing. Even though you created a composite key, if the relationship is still ambiguous or if the data model allows both tables to filter each other, Power BI might be duplicating values during evaluation.

Use TREATAS to control context explicitly: Instead of relying on automatic relationship filtering, use TREATAS to apply the correct filter context in your measure.

If you are still facing the issue, it is advisable to reach out to Microsoft Support for better assistance

You can submit a ticket through the Microsoft Power BI Support Portal:

https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket.

Thanks 

[PhilSap]

@sergej_og sessions is a static number

[v-karpurapud]

Hi @PhilSap 

Welcome to the Microsoft Fabric Forum.

Thank You @sergej_og for responding on this topic.

The issue occurs because the relationship between the two tables relies only on the 'Country' column. This works initially but fails when time-series data is introduced, as there are multiple rows per country for different months.

This makes the 'Country' field no longer unique in either table, creating a many-to-many relationship that Power BI struggles to handle due to ambiguity in the joins. The core problem is the use of a non-unique key, which becomes problematic as the data complexity increases.

To address this, it's recommended to create a composite key using both 'Date' and 'Country', ensuring each row remains unique and the relationship between tables is clearly defined.

Create a calculated column in both tables (e.g., CountryDateKey) that combines Date and Country:

CountryDateKey = [Date] & "-" & [Country]

Then establish the relationship using this new CountryDateKey column.


If this post helps , kindly mark it as Accepted Solution. 

Thank You!

[PhilSap]

@v-karpurapud that did work

So the next step would divide sessions by clicks

For march the table with sessions is 1,368,209 and the other table clicks is showing 1,444,106 which should show 0.95 but is instead showing 27.17. 

[v-karpurapud]

Hi @PhilSap 

To get the correct sessions-to-clicks ratio, create a DAX measure (not a calculated column) that sums both values before dividing. This avoids row-level calculation errors.
 

Since your session data is in SessionsTable and click data in ClicksTable, and both are linked by CountryDateKey, use this DAX measure:
 

SessionToClickRatio =

DIVIDE(

    SUM(SessionsTable[Sessions]),

    SUM(ClicksTable[Clicks]),

    0

)

This ensures the division is based on total sessions and clicks in the current context (e.g., by month or country), and gives you the correct result like 0.95 for March.

If this post helps, kindly mark it as Accepted Solution.

Thank You!

[PhilSap]

Unfortunley , I am still getting the same 27.17. I have a many to one relationship that has the data flowing in both directions.  I've been messing around with it but seems creating the key didnt help

heres how to session table is set up

[v-karpurapud]

Hi @PhilSap 

While I may not have full visibility into the specific structure of your dataset, I have created a sample .pbix file to demonstrate one possible approach to implementing the desired logic. I have included relevant screenshot and attached the .pbix file for your reference.Please take a moment to review them and see if this solution aligns with your requirements. 

If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution. If this post helps, kindly, mark it as Accepted Solution.

Thank You!
 

[PhilSap]

I'm still having issues. Here is a sample file of what I am working with. Appreciate all the help!

https://we.tl/t-z8nAlWSzgg

[v-karpurapud]

Hi @PhilSap 

I am unable to open the file you shared as it is prompting me to upgrade to the paid version. If possible, could you please share the file in another format?

[PhilSap]

hey @v-karpurapud  this is a .pbit file so hopefully this works!

https://we.tl/t-vPlhPv5IHt

[v-karpurapud]

Hi @PhilSap 

When I try to refresh, I getting the error shown in the snapshot, and I am unable to see the data. This makes it difficult to proceed without the necessary information. I am attaching my sample PBIX file for your review. If possible, please add the tables from your report to the PBIX.

Thank You!

[PhilSap]

Sorry about that. I dont know how to attached files to a post but here is a wetransfer link again. This is a sample of data. I noticed you had a 3rd table created in your scenario so that could be my problem 

https://we.tl/t-stvGhXVw5n

[v-karpurapud]

Hi @PhilSap 

We sincerely regret the inconvenience this issue has caused.After a thorough analysis, we’ve identified that the behavior is due to bidirectional filtering within the data model, leading to complex evaluation challenges. Since this appears to be a deeper issue involving Power BI’s internal relationship handling, we believe it would be best to engage Microsoft Support for more specialized assistance, as suggested by @ABD128 .

We hope your issue is resolved soon and appreciate your understanding.


Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[v-karpurapud]

Hi @PhilSap 

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-karpurapud]

Hi @PhilSap 
Thank you for your patience as we continue to investigate the issue.

I apoligize for the delayed response. Based on our analysis, it seems the unexpected output like the 27.17 ratio you're seeing may be caused by the bidirectional filtering set on a many-to-one relationship within the data model. Bidirectional filters can sometimes lead to ambiguity or unintentional context propagation, causing duplicated values and incorrect aggregations. Although a composite key was introduced, the underlying relationship behavior might still be contributing to the issue.

We are actively refining the data model to ensure accurate results and will keep you updated as soon as we achieve the expected outcome.

In the meantime, could you please test the solution suggested by @ABD128  and let us know if it addresses the issue on your end? Your feedback would be very helpful in narrowing down the resolution.

Regards,
Karpurapu D.

[sergej_og]

But you have a calendar table connected to your fact table?

Which measure do you use to calculate "Sessions"?