Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Hartmut
Regular Visitor

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

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?

 

4 REPLIES 4
v-jianboli-msft
Community Support
Community Support

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):

vjianbolimsft_0-1668068767483.png

Then create two table visuals, apply the filters individually:

vjianbolimsft_1-1668068837420.png

vjianbolimsft_2-1668068861136.png

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:

vjianbolimsft_3-1668069032769.png

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.

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
Community Support
Community Support

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:

vjianbolimsft_0-1667959033155.png

 

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.

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.