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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vincentjdc
Frequent Visitor

Multiples JOINS same table

Hi,

 

I manage multiples tools and multiples databases in my company.

My CFO want to use Power BI to make reports based on the databases.

 

we are facing a problem with relationships. Multiples tables (orders, invoices, supplier_ordes, supplier_invoices, ...) reference to a same table (project) that leads to an "ambiguity error" forcing us to activate and deactive relationships in the relationships manager.

 

How can we avoid that ?

 

Is there a way to indicate witch relation should it use ? (Like the JOIN ... ON in SQL ?)

 

Thanks for your help,

 

Vincent

5 REPLIES 5
vincentjdc
Frequent Visitor

@tex628 Thanks for your reply. It's working but it quickly becomes slow in power query and for tables with 1k to 5k rows. Maybe I'm doing it wrong or to much in power query

 

@v-yanjiang-msftThanks for your reply too. I understand why there is an ambiguity and the concept of different paths. The problematic is to tell to bowerbi "in that vizualization use that path, in this other vizualization use the other path, ..."

Let's say that we have the tables "order" and "invoice" that are related. Each one can be related to a "customer" table. In some case, we need to list the orders with theirs customers, in other case list the invoices with the customers. There are two paths, both could be used in different contexts.

I did in power query but it begins to be very slow.

That sounds very strange. Merging queries at 1-5k rows should not pose any issue at all. How slow is "slow"? 

/ J


Connect on LinkedIn

Hi @vincentjdc ,

Could set one path to inactive, and use the userelationship-function in the calculation when the relationship is needed.

 

Best Regards,
Community Support Team _ kalyj

v-yanjiang-msft
Community Support
Community Support

Hi @vincentjdc ,

A model is ambiguous when there are multiple paths between tables. In an ambiguous model, the engine has multiple options when transferring a filter from one table to another. Therefore, it either finds a preferred way to transfer the filter, or it raises an error.

Look at the model and check where the ambiguity is. You need to find two tables that are linked through different paths, following the arrows of the cross-filter.

Additionally, star schema is often recommended in Power BI modeling.

 

Related document for your reference: Understand star schema and the importance for Power BI - Power BI| Microsoft 

Bidirectional relationships and ambiguity in DAX - SQLBI.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tex628
Community Champion
Community Champion

Hi Vincent, 

You have the merge functionality in Power Query which you can use to join tables:

tex628_0-1652193786216.png


It will let you assign the keys that you want to be used as well as the direction and logic of the join. (Left/right/inner/outer etc)

Br, 
Johannes 


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.