Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shaebert
Helper III
Helper III

3 Table Relationship Question

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. 

 

Screenshot 2024-06-11 at 11.52.33 AM.png

1 ACCEPTED SOLUTION
shaebert
Helper III
Helper III

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

 

Screenshot 2024-06-13 at 2.31.41 PM.png

View solution in original post

7 REPLIES 7
shaebert
Helper III
Helper III

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

 

Screenshot 2024-06-13 at 2.31.41 PM.png

Uzi2019
Super User
Super User

Hi @shaebert 

 

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

Uzi2019_0-1718171956289.png

 

 

I hope I answered your question!

 

 



Don't forget to give thumbs up and accept this as a solution if it helped you!!!

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
Super User
Super User

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

 

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

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/

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors