3 Table Relationship Question

[shaebert]

I feel like there is a simple answer to my question, but I can't figure it out. 

I want to see the amount of Commission by Invoice Date with Date and Campaign slicers. I cannot create a relationship between the Campaign and Commission table to do this the way I want to. 

[shaebert · Accepted solution]

The solution was to create a bridge table. See below:

[Uzi2019]

Hi @shaebert 

Just simply follow this relationship. avoid creating relationship between Date > Campaign table 

I hope I answered your question!

[shaebert]

Thanks for taking a look, but this does not work (maybe I needed to provide further details). 

I want to create a Date slicer, that can filter the Campaigns (count) by the date they started, and/or by the Sum of Commission by the Date it was invoiced.

The solution you provided would not accuratly depict the number of campaigns that took during the time period, it would only depict the number of campaigns associated to Commission that was invoiced during that time period (invoice dates can take place up to a year after the campaign start date).

[AlexisOlson]

Try changing the relationships Date --> Campaign and Date --> Commision to both be one-directional and one-to-many.

Currently, you can't create the desired relationship because Campaign and Commission already filter each other indirectly via Date, and adding another relationship between them would create ambiguity in how they should filter each other.

Further reading:

https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

https://community.fabric.microsoft.com/t5/Community-Blog/Understanding-Ambiguity-in-Power-Data-Model...

[shaebert]

Thanks for the suggestion; I tried this but was still given the similar ambiguity error when trying to connect the Commission and Campaign tables.

[AlexisOlson]

Ah, you're right. You would still have multiple paths for Date to filter the other tables: either directly e.g. Date --> Commission or indirectly e.g. Date --> Campaign --> Commission.

I think the problem boils down to having Date serving two different purposes simultaneously. You probably don't actually want Date to filter campaign date creation and invoice date simultaneously (you'd only see situations where both of those dates are the same), so one of those relationships should probably be inactive or else you might want separate date tables.

Further reading:

https://www.sqlbi.com/articles/using-cross-highlight-with-order-and-delivery-dates-in-power-bi/

[shaebert]

Actually Yes I want to simultaneously show the number of campaigns separate from the sum of commission that were generated in the timeframe selected from the date table. I don't want the commission amount to be dependent on the campaign or vice versa. Maybe creating two date tables well accomplish my goal.