Forum Discussion

Hartmut's avatar
Hartmut
Regular Visitor
3 years ago

Nested dynamic queries, easy in SQL - difficult in PowerBI/DAX

Assume a single large source table, which must be queried in two steps: Tab1:  Key_A   Key_B   Key_C   Val  a1 b1 c2 1 a1 b2 c1 5 a2 b3 c1 3 ...
DAX
drillthrough
join
nested
v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

Hi Hartmut ,

 

Please try:

Table 2 = 
var Qry1 = SUMMARIZE(FILTER('Table',[ Key_A ]="a2"&&[ Key_C ]="c1"),'Table'[ Key_B ],"SumVal",SUM('Table'[ Val ]))
var Qry2 = SUMMARIZE(FILTER('Table',[ Key_A ]="a2"&&[ Key_C ]="c2"),'Table'[ Key_B ],"SumVal",SUM('Table'[ Val ]))
var Qry3 = SUMMARIZE(ADDCOLUMNS(Qry1,"Diff",[SumVal]-SUMX(FILTER(Qry2,[ Key_B ]=EARLIER('Table'[ Key_B ])),[SumVal])),'Table'[ Key_B ],[Diff])
return Qry3 //Change the variable after return to the result you want

Final output:

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hartmut's avatar
Hartmut
Regular Visitor
3 years ago

Hello!

First, thanks for your effort to think about the problem!!!
You show an interesting solution for "Qry3", with the "EARLIER" function (I didn't have this idea).
With static filter values, I once had this set up similarly.
But the Microsoft helpfile warns about performance problems with large tables when using the "EARLIER" function. My suggestion would be:

Table =
var Qry1 = SUMMARIZE(FILTER('Tab1',[ Key_A ]="a2" && [ Key_C ]="c1"),'Tab1'[ Key_B ],"Sum1",SUM('Tab1'[ Val ]))
var Qry2 = SUMMARIZE(FILTER('Tab1',[ Key_A ]="a2" && [ Key_C ]="c2"),'Tab1'[ Key_B ],"Sum2",SUM('Tab1'[ Val ]))
var Qry3 = SELECTCOLUMNS(NATURALLEFTOUTERJOIN(Qry1, Qry2),"Key_B",Tab1[Key_B],"Diff",[Sum1]-[Sum2])
return Qry3

 

An important part of the problem and probably my real problem is the variable filter.

What do you mean by “…Change the variable after return to the result you want”? How to replace the hard coded filter value "a2" in DAX with a parameter that contains the Drillthrough value of PowerBI on a detail page (so in our example either "a1" or "a2") which makes the result table not static anymore?

Somehow, I fear that tables must always be static. But I also can't believe that such a simple analysis should not be possible in PowerBI with DAX.