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

[Hartmut]

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
a2	b4	c2	8
a2	b4	c1	1
a2	b1	c2	4

With a variable drillvar = "a2" and two queries you would get:

Qry1: SELECT Key_B , Sum(Val) AS SumVal FROM Tab1 WHERE Key_C = 'c1' AND Key_A = drillvar GROUP BY Key_B	Qry2: SELECT Key_B , Sum(Val) AS SumVal FROM Tab1 WHERE Key_C = 'c2' AND Key_A = drillvar GROUP BY Key_B
Key_B   SumVal    b3   3   b4   1	Key_B   SumVal    b1   4   b4   8

In the next step, both queries would be joined (the "Nz" function is used here to handle null/blank values):

Qry3:

SELECT Qry1.Key_B, Nz(SumVal1) - Nz(SumVal2) AS Diff

FROM Qry1 LEFT JOIN Qry2 ON Qry1.Key_B=Qry2.Key_B

Result:

Key_B	Diff
b3	3
b4	-7

The task seems trivial. I think in PowerBI with DAX plus a Drillthrough value instead of SQL plus a variable it should be easy to do. Unfortunately, I've been racking my brains for two days on how to solve this efficiently, i.e. especially with a large source table.

Does anyone here have an idea?

[v-jianboli-msft]

Hi @Hartmut ,

Calculated tables are best for intermediate calculations and data you want to store as part of the model, rather than calculating on the fly or as query results. Calculated tables are recalculated if any of the tables they pull data from are refreshed or updated, unless the table uses data from a table that uses DirectQuery; in the case with DirectQuery, the table will only reflect the changes once the dataset has been refreshed.

So if you want the queries be dynamic, you may need to use visuals:

First, use the column [Key_A] to create a slicer(the function of the slicer just like the variable drillvar):

Then create two table visuals, apply the filters individually:

Query3 just like the Query2, the only difference is the measure:

Diff = SUM('Table'[ Val ])- CALCULATE(SUM('Table'[ Val ]),FILTER(ALL('Table'),[ Key_C ]="c2"&&[ Key_B ]=MAX('Table'[ Key_B ])))

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]

Hello Jianbo,

thank you very much for your interest in the problem!!! Unfortunately, your solution contains a logical error.

At first you probably mean: "Query3 just like the Query1(!), the only difference is the measure". But this is surely just a misspelling.

The actual error lies in the filter of your measure. The function ALL cancels the effect of the slicer of Key_A and the subtotal is formed over the wrong rows. Omitting ALL would not be a solution either, because Query1 contains only values with "c1". Because the visual for Query3 would therefore only contain rows of "c1", the filter would not find any rows with "c2".

For illustration: Simply add a row with the keys "a1", "b4" and "c2" to the source table. The result of query3 should not change because of "a1". But your measure does.

Since the rows within the source table are related (via Key_B), the evaluation must be performed in this logical order

1. split table (Key_C)
2. select subsets (Key_A)
3. aggregate each table (Key_B)
4. join both intermediate tables (Key_B) and perform calculations with aggregated values

Steps 1 to 3 can be combined in one query each (Query1 and Query2). So, this is what I mean with "two steps" in my Problem Description.

Our static table expressions from the previous post do this correctly. Unfortunately, they only allow a static subset (Key_A). And that is what I find hard to believe with this sophisticated PowerBI/DAX.

many greetings!

[v-jianboli-msft]

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]

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.