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 ,

 

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's avatar
Hartmut
Regular Visitor
3 years ago

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!