Forum Discussion
Creating Relationships with duplicative values
Hi Anonymous
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
Hi Anonymous
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.
Hi Anonymous
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!
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
Hi Anonymous
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!
I'm still having issues. Here is a sample file of what I am working with. Appreciate all the help!
Hi Anonymous
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?