Forum Discussion
direct query and import tables
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
5 Replies
Is the look up in a Measure or Calculated Column?
Hi Anonymous
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!
Hi Anonymous
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-relationships
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)
