Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a very large table (import mode) and a very small table (direct query mode, just because it's changing very frequently). Effectively I need a "outer join" her, so a lookup in the small table if there's a related information enhancing the big table, in case there's nothing I'll take some default from the big table. So essentially
value = if(isblank(related(smalltable(lookup_field)),largetable(default_field),related(smalltable(lookup_field)))
This works fine if both tables are in import more. However if I use the small table in direct query mode I get an error that there's no relation between the two tables. Just changing the small table to import mode fixes the error but not my problem 😉
Any idea? Would that work at all in regards to performance?
Thanks,
Thomas
Solved! Go to Solution.
@TePe ,
There're several limitations of data model regarding direct query mode. For example, the bidirectional cross filtering is not supported in direct query mode. In general, I would suggest you use other expressions instead of related function. For exmaple, use pattern like below:
value =
VAR Current_Id = largetable[Id]
RETURN
IF (
ISBLANK ( largetable[default_field] IN VALUES ( smalltable[lookup_field] ) ),
largetable[default_field],
CALCULATE (
MAX ( smalltable[lookup_field] ),
FILTER ( smalltable, smalltable[Id] = Current_Id )
)
)
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@TePe ,
There're several limitations of data model regarding direct query mode. For example, the bidirectional cross filtering is not supported in direct query mode. In general, I would suggest you use other expressions instead of related function. For exmaple, use pattern like below:
value =
VAR Current_Id = largetable[Id]
RETURN
IF (
ISBLANK ( largetable[default_field] IN VALUES ( smalltable[lookup_field] ) ),
largetable[default_field],
CALCULATE (
MAX ( smalltable[lookup_field] ),
FILTER ( smalltable, smalltable[Id] = Current_Id )
)
)
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I guess that will work but performance will be not very nice (we're talking about >150million records in the large table). We're currently checking other options to model this challenge...
Thanks,
Thomas
Perhaps try LOOKUPVALUE instead?
User | Count |
---|---|
113 | |
106 | |
83 | |
67 | |
42 |
User | Count |
---|---|
157 | |
110 | |
84 | |
78 | |
60 |