Direct Query - related function

[Anonymous]

Hi Expert

i cannot get my related table calculated column to work when using direct query in my Power Bi model.. Is this possible? or is there a work around?

[kmcaloon]

RELATED is not supported with limited relationships.   see Microsoft Documentation limited relationships 

[Anonymous · Accepted solution]

According to http://dax.guide/related, RELATED is supported in DirectQuery:

RELATEDTABLE, on the other hand, works only in measures in DirectQuery:

So, if you can't get it to work in the right context, it means you're doing something wrong.

[Anonymous]

that is what i cannot work out. Exchnage rate table relationship to FACT table is store id to store id.

[Anonymous]

error message when using related

[AndyFL]

I get the same all the time as well.  The workaround would be a calculated column in your target table (FactSalesOrderTable):

Exchange Rate =
CALCULATE (
AVERAGE ( 'ExchangeRate'[Exchange Rate] ),
FILTER (
'ExchangeRate',
'ExchangeRate'[Currency] = 'FACTSalesOrderTable'[Currancy]
&& 'ExchangeRate'[OtherColumn] = 'FACTSalesOrderTable'[OtherColumn]
))

It basically works like an averageifs funtion in Excel. Can also be used with a sum for a sumifs.  

It's not the best idea as it's ressource intensive and slow, as mentioned below, but this will get the job done. And not too bad if you've got limited data in your fact table. 

[Anonymous]

RELATED needs a row context to work. Do you have a row context when you use this function above? I can't even tell from what you posted what it is you're trying to achieve...

Are you maybe trying to add the rate to each row in the fact table by creating a calculated column? That would be a VERY BAD IDEA.

[pfInigo]

Hi - i know this topic is old, but can you elaborate on why this would be so bad?