direct query and import tables

[djm7]

good afternoon

I have two tables the large table is a direct query table in ssas

the small table is an import table with a few hundred rows.

I join the two 

but my next step is to use the dax related function to look up a value in the second small table, but I get

the following error

The column ...................... either doesn't exist or doesn't have a relationship to any table available in the current context.  the relationship between the two table is many to one, but I still get the ( ) on the line connecting the two tables and I assume this is because one is a direct query table and the second is an import table.

is this correct, anyway to do the lookup without using the related and without making the first large table into an import table

[v-tangjie-msft]

Hi @djm7 ,

Whether the advice given by danextian and suparnababu8 has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

[danextian]

Hi @djm7 

Yours is a case of a limited relationship

• many-to-many
• from one source group to another (import to direct query and vice versa)
• 

Please see this documentation https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#limited-... 

Alternative (or not), these approaches I couldn't use with SQL server when the calculation crosses between two different source groups:

• LOOKUPVALUE
• RELATED
• CALCULATE(....FILTER(...
• measures that try to propagate a virtual relationship - using TREATAS or table1[column] in values(table2[column)
•  

[suparnababu8]

Hi @djm7 

I think RELATED dax function doesn't work well across these mixed storage modes. It should be used for same storage modes only.  

I am recommending you to use LOOKUPVALUE dax, this fuction may work better for mixed storage sceniories.

Please go through this Solved: RELATED Function with a Direct Query Table - Microsoft Fabric Community accepted solution provided by @johnt75 . You will get the better clarity.

Thanks!

[Tutu_in_YYC]

Is the look up in a Measure or Calculated Column?

[djm7]

calculated column