DAX Performance Tuning - Avoid using ALL

RobbeVL90 · ‎04-25-2023

Hi,

I've been out of the DAX game for a little too long I think :).

I have a table as following :

ccountId PartnerId Transactions FirstTransaction row_num Industry Private_Public Result

6906	19	557	2022-01-01	1	Financial services	Private	Private only
6906	20	557	2022-01-02	2	Financial services	Private	Private only
6906	32	30	2022-01-05	3	Government	Public	Private + Public
6906	738	2	2022-01-12	4	Government	Public	Private + Public
6906	82	5	2022-01-13	5	null	Private	Private + Public
6906	74	1	2022-06-11	6	Financial services	Private	Private + Public
6906	56	1	2022-06-27	7	Insurance	Private	Private + Public

Keep in mind this table only has 1 Accountid, but in reality my table has a few millions.

What I am trying to do is count the distinct accounts per month, but if there is no month, it should show the previous value of that account.

The output would be a barchart that shows a bar per month. MArch should in this case show the feb value.

I would like to put the Result Column as a legend.

My Code so far:

VAR SelectedYearMonth =
    SELECTEDVALUE ( DIM_Date[Year Month] )
VAR Tbl =
    ADDCOLUMNS (
        FILTER (
            ALL ( FACT_Transactions ),
            FACT_Transactions[Firsttransaction] <= EOMONTH ( SelectedYearMonth, 0 )
        ),
        "Rank",
            RANK (
                DENSE,
                FILTER (
                    FACT_Transactions,
                    FACT_Transactions[Firsttransaction] <= EOMONTH ( SelectedYearMonth, 0 )
                ),
                ORDERBY ( FACT_Transactions[Firsttransaction], DESC ),
                PARTITIONBY ( FACT_Transactions[accountid] )
            )
    )
RETURN
    COUNTROWS ( FILTER ( Tbl, [Rank] = 1 ) )

If this Code should be Correct, but when I apply the ALL, I run out of memory!
How should this be optimized to get the same result ?

lbendlin · ‎04-29-2023

Have a look at how the Power BI team writes their code - examples are in the Quick Measures, or in the Performance Analyzer for visual queries. You will see a lot of SUMMARIZECOLUMNS and TREATAS etc.

Use DAX Studio to examine your query plan - look for excessive number of records. Then go to sqlbi.com and watch a couple of videos on how to optimize your DAX code.

If you want us to help here please provide meaningful sample data.