Often we need to write many lines of code in SQL to derive retention year over year. Generally, we join the main fact table to itself and then add a condition that a donor this year has to also be a donor next year.
The code below accomplishes the same things in DAX.
SUMMARIZE(
— context filter —
FILTER(dimDate, dimDate[Year] >= 2019),
dimDate[Year],
“Annual Retention”,
COUNTROWS(
— 1st loop —
CALCULATETABLE(
VALUES(Donations[Donors]),
FILTER(ALL(Campaigns), Campaigns[Channel] = “Digital”),
— 2nd loop —
CALCULATETABLE(
VALUES(Donations[Donors]),
FILTER(ALL(dimDate), dimDate[Year] = MIN(dimDate[Year]) + 1),
ALL(Donations)
)
)
)
)
Now let’s review this pattern step by step.
EVALUATE
The top of the code is the group by context. We are looking at the year after 2019. Generally, the visuals in PowerBI provide that context, however, in DAX Studio it is useful to build a simple pivot to generate data similar to actual reports.
SUMMARIZE(
— context filter —
FILTER(dimDate, dimDate[Year] >= 2019),
dimDate[Year],
“Annual Retention”,
Countrows command counts donors in the final filtered table.
COUNTROWS(
Donor base is defined for the reference year for the Digital channel only. Values ensure distinct count. CALCULATETABLE derives the base donors and allowed modification based on the second CALCULATETABLE.
CALCULATETABLE(
VALUES(Donations[Donors]),
FILTER(ALL(Campaigns), Campaigns[Channel] = “Digital”),
Second CALCULATETABLE is the filter for the first set of donors. In the main donor table, they have to have a donation record in the next year after the reference year. ALL filter is necessary, otherwise, the query will have mutually exclusive conditions.
CALCULATETABLE(
VALUES(Donations[Donors]),
FILTER(ALL(dimDate), dimDate[Year] = MIN(dimDate[Year]) + 1),
ALL(Donations)
)
)
)
)
