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?