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!
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.
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.
var SCALED = SUMMARIZE(Table2, [Date], [key1], [key2], [Scale], "ret$", SUM('Table1'[value$]))