Subqueries in DAX

Question

How can I write this query in DAX ?

SELECT TimeID, Code,(SELECT SUM(qty) FROM cteRanked c2 WHERE c2.rownum <= c1.rownum and c2.Code=c1.Code)
  FROM cteRanked c1;

Thank you

Looks like a Cumulative Total: https://www.daxpatterns.com/cumulative-total/ — David Browne - Microsoft, Feb 20 '18 at 15:31
David's suggestion is spot on. Also your tsql may be optimised using a windows function: SUM(qty) OVER(PARTITION BY Code ORDER BY rownum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — M O'Connell, Jun 17 '18 at 12:13

score 0 · Answer 1 · answered Oct 04 '20 at 18:39

Considering that no grouping is specified in the SQL query, this might work

EVALUATE
SELECTCOLUMNS(
    ADDCOLUMNS(
        cteRanked,
        "QtyRT",
            VAR LastRowNum = cteRanked[rownum]
            RETURN
                CALCULATE(
                    SUMX( cteRanked, cteRanked[qty] ),
                    REMOVEFILTERS( cteRanked ),
                    VALUES( cteRanked[Code] ),
                    cteRanked[rownum] <= LastRowNum
                )
    ),
    "TimeID", cteRanked[TimeID],
    "Code", cteRanked[Code],
    "QtyRT", [QtyRT]
)

First we iterate over the whole cteRanked table, adding the column QtyRT (quantity Running Total), that contains the sum of all the rows with the same Code with a rownum <= of the saved rownum. Then we select just the required columns using SELECTCOLUMNS.

Subqueries in DAX

1 Answers1