Forum Discussion

RobbeVL90's avatar
RobbeVL90
Frequent Visitor
3 years ago

DAX Performance Tuning - Avoid using ALL

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

6906195572022-01-011Financial servicesPrivatePrivate only
6906205572022-01-022Financial servicesPrivatePrivate only
690632302022-01-053GovernmentPublicPrivate + Public
690673822022-01-124GovernmentPublicPrivate + Public
69068252022-01-135nullPrivatePrivate + Public
69067412022-06-116Financial servicesPrivatePrivate + Public
69065612022-06-277InsurancePrivatePrivate + 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 ? 

1 Reply

  • 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.