DirectQuery + COMBINEVALUES() + RELATED()

[SFLPBI]

I am relating some tables together using the COMBINEVALUES() function.  It works great.  As soon as I reference one of the values i.e. x = Table1[val1] * RELATED(Table2.[val2]), the SQL generated starts to inner join Table2 2 times.  One joining on the compound key (correctly) and the other using the concatonated values from both tables.

Any insight or work around will be greatly appreciated!

Thanks

[Anonymous]

I'm having the same problem, but I don't think the measure change will work for me, as I'm trying to get attributes across related tables.

For example, two tables are related via three columns.  Then a third table is related via a combination of other columns from those two tables.  I use RELATED in the many side of the two tables with COMBINEVALUES, but it does the concatenation thing.  

[v-lili6-msft]

hi, @SFLPBI 

Please check this blog if it could help you:

https://www.sqlbi.com/articles/using-combinevalues-to-optimize-directquery-performance/

Regards,

Lin

[SFLPBI]

Thank you @v-lili6-msft  for taking the time to reply. 

Unfortunately that article does not help, as it only shows how to create relationships with COMBINEVALUES().  The initial subquery/join is fine.  When I added the RELATED() function, it adds an additional subquery/join based on the concatonated values instead of the individual components of the COMBINEVALUES() function.  So the resulting query ends up with TWO subqueries; the first joining correctly and the second on the concatonated string.

Kind regards,

Kevin

[SFLPBI]

By the way, the below "DAX pattern" produces the correct SQL joins and runs reasonably fast, but it doesn't return a single result from the SQL server side.  This causes the "1 million row" limit error for my particular application.

scaledSum$ =

    var SCALED = SUMMARIZE(Table2, [Date], [key1], [key2], [Scale], "ret$", SUM('Table1'[value$]))

    return SUMX(SCALED, [Scale] * [ret$])

[SFLPBI]

Another update.  The below code appears to be doing the trick.  I am going to continue to test this out.

scaledSum$ = SUMX(Table1, RELATED(Table2[Scale]) * Table1[value$]))