cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SFLPBI Frequent Visitor
Frequent Visitor

DirectQuery + COMBINEVALUES() + RELATED()

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

4 REPLIES 4
Community Support Team
Community Support Team

Re: DirectQuery + COMBINEVALUES() + RELATED()

hi, @SFLPBI 

Please check this blog if it could help you:

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

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SFLPBI Frequent Visitor
Frequent Visitor

Re: DirectQuery + COMBINEVALUES() + RELATED()

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 Frequent Visitor
Frequent Visitor

Re: DirectQuery + COMBINEVALUES() + RELATED()

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 Frequent Visitor
Frequent Visitor

Re: DirectQuery + COMBINEVALUES() + RELATED()

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$]))

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 369 members 3,600 guests
Please welcome our newest community members: