Forum Discussion

TePe's avatar
TePe
Icon for Helper IV rankHelper IV
6 years ago
Solved

Outer Join in a Composite Model

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

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    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.

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      TePe
      Icon for Helper IV rankHelper IV
      6 years ago

      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