RELATED function not working despite a join

[mp390988]

Hi,

I have this model:

I am trying to create a calculated column to bring in the DealerID from the Clients Table into my Payments Table.

But i get an error saying: The column 'Clients[DealerID]' either doesn't exist or doesn't have a relationship to any table available in the current context.

Why is this happening?

Thank You

[danextian]

Hi @mp390988 

That is a limited relationship between DQ and import and what you're experiencing is a known limitation.

Model relationships in Power BI Desktop 

[mp390988]

Ah I see, thanks @danextian .
So I will use @shashiPaul1570_ suggestion of LOOKUP instead then.

[danextian]

Have you tried LOOKUPVALUE? Did  it work? The last time I tested it with a limited relationship, it didn't work.

[shashiPaul1570_ · Accepted solution]

@mp390988  

Glad that helped! 
Yes, LOOKUPVALUE is a good alternative when RELATED doesn’t behave as expected, especially if the relationship setup is tricky. Just make sure the Clients[ClientID] column is unique, so you always get a single match.

Happy reporting, and if this solved your issue, please don’t forget to give a Kudos 👍 and mark it as Accepted Solution ✅ so others can benefit too.

Thanks
Shashi Paul 

[mp390988]

I can see a relationship betweent the Clients and Payments table but I just noticed some weird thing as marked in red in the snapshot:

The relationship between these 2 tables is active:

[shashiPaul1570_]

Hi @mp390988 

Thanks for sharing the update.
For some reason, I am unable to see the screenshot that you might have shared. 
Since the relationship is active, here are a few things you can check

1. Cardinality & Direction

   • Ensure it’s a 1 → * relationship with Clients on the “1” side and Payments on the “*” side.

   • If the arrow direction is set incorrectly (e.g., both sides * or a two-way cross filter causing ambiguity), the Related() function may fail.

2. Column Reference

   • Double-check that the column name you’re trying to bring in is exactly Clients[DealerID] and not a different name (sometimes spaces, casing, or hidden columns cause confusion).

3. Multiple Relationships

   • If there are multiple paths between Clients and Payments, Power BI may deactivate one and mark it with a dotted line. In such cases,  Related() won’t work unless the active path is the correct one.

4. Alternative Approach

   • If the model design makes Related() fail, you can still achieve it with

DealerID =
LOOKUPVALUE(
Clients[DealerID],
Clients[ClientID], Payments[ClientID]
)

This works even if the relationship is not straightforward, as long as Clients[ClientID] is unique.

If you just want to use the Related() function only, just try the same senario in different file with sample tables which fulfills the conditions of Related(). Hopefully it will give you better idea. 

Hope this helps! Please give a Kudos 👍 if useful, and mark as Accepted Solution ✅ once it resolves your query.

Thanks 
Shashi Paul 

[shashiPaul1570_]

Hi @mp390988 
RELATED() only works in a calculated column when there is an active 1→ relationship* from Clients (1) to Payments (*) and a single, unambiguous path.

In Model view, please ensure
Clients[ClientID] 1 —— * Payments[ClientID] (Active = Yes).

Then use in Payments

DealerID = RELATED(Clients[DealerID])

If you can’t make that relationship active (or it’s M2M), use:

DealerID = LOOKUPVALUE(Clients[DealerID], Clients[ClientID], Payments[ClientID])

Ensure Clients[ClientID] is unique; otherwise LOOKUPVALUE will error.

Please find this artical for more details.
https://www.datacamp.com/tutorial/power-bi-related-dax-function?utm 

If this solves it, a Kudos 👍 and Accepted Solution ✅ would be awesome!

Thanks 
Shashi Paul