Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
@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
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
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.
Hi Vincent,
You have the merge functionality in Power Query which you can use to join tables:
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
User | Count |
---|---|
134 | |
72 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |