Outer Join in a Composite Model

[TePe]

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

[v-yuta-msft · Accepted 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]

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

[Greg_Deckler]

Perhaps try LOOKUPVALUE instead?