New/Dropped Customers Dax Performance Issue

DAX DAX Calculations
1

Hi Guys,

I have a two-fold issue that I have searched for but have not seen a solution for yet inside the existing forum topics.

We have a pretty big table that contains customer account numbers with their current bank balances across different product portfolios e.g. Home Loans, Car Loans, Fixed Deposits, etc. The requirement from the business was that they needed a view of the day on day movements across 1) Bank Balance 2) total customer counts. Both these measures needed to be shown from a product perspective and an age bucket perspective i.e. how long does this customer have before finishing their loan or how long have they fixed their deposit for.

So essentially I was using the following columns Transaction ID, Product Name, Balance, Settlement Date, Business Date

I created a bucketing column by first determining the Age of the account by subtracting the Reporting Business Date (e.g. Yesterday) from the settlement date (e.g. 25 Jan 2025). I would then use this column to create a second calculated column which uses the Age of the account in an IF statement to determine the bucket into which the account age fell into using the below logic
IF ‘Green Model’[AGE] < 91, “0m - 3m”,
IF ‘Green Model’[AGE] < 182, “3m - 6m”,
IF ‘Green Model’[AGE] < 364, “6m - 12m”,
IF ‘Green Model’[AGE] < 728, “1y - 2y”,
IF ‘Green Model’[AGE] < 1092, “2y - 3y”,
IF ‘Green Model’[AGE] <1457, “3y - 5y”,
IF ‘Green Model’[AGE] < 1820, “5y - 7y”,
IF ‘Green Model’[AGE] <= 2548, “7y - 10y”,
IF ‘Green Model’[AGE] > 2548, “Over 10y”,
“N/A”

Once this was achieved, I created a measure that sought to calculate the transactions that exist on the current reporting day but not on the previous reporting day based on the selection of any two days on a filter. Below is the logic for this measure.

#TxnInCurrNotInPrev =

VAR dC = CALCULATE(

MAX( 'Date'[BusinessDay]),

ALLSELECTED( 'Date' )

)

VAR dP = CALCULATE(

MIN( 'Date'[BusinessDay]),

ALLSELECTED( 'Date' )

)

return
COUNTROWS(

EXCEPT(CALCULATETABLE(VALUES('Green Model'[TXN_ID]),'Date'[BusinessDay] = dC)

     ,(CALCULATETABLE(VALUES('Green Model'[TXN_ID]),'Date'[BusinessDay] = dP)

      ) ) )

So the problems I am facing are that with the performance of the report once I start introducing the Buckets into the Matrix in addition to the Product Name. It takes around 2mins for the matrix to load (Note: in the attached image the #DroppedTXNs should be #NewTXNs)
image

Then the second issue is that when I do introduce the Buckets attribute the logic for some reason also includes a count of deals which, due to maturity, have moved from one bucket to another as “New”, however as you can see on the image the total count is correct for AffordableHousing (3) but when we do a drilldown to age buckets it shows a total count of (9).

Below is the model of the report. pretty simple but the table is pretty big i.e. approx 1.4m records a day

image
image894×928 64.9 KB

My guess is that there is a glitch somewhere for which I need to do the bucketing after the EXCEPT logic instead of doing it at a data view level.

Please assist with any views that I could be missing

2

@Lazola ,

Optimization is not my area of expertise, but three things come to mind here to try in order to speed the bucketing calculation up significantly:

  1. Try building it as a measure, instead of a calculated column
  2. Instead of the nested IF statements, try using a SWITCH construct instead
  3. In that SWITCH construct, try assigning variables according to the rules at the end of the article below in order to maintain the much faster “short-circuit evaluation” conditions.

I’m also looping in Enterprise DNA Expert @AntrikshSharma who has amazing knowledge of DAX optimization, and I’m sure will have outstanding advice about how to optimize the DAX here.

– Brian

3

@BrianJ Not enough information here, EXCEPT can be slow over high granualrities, VALUES can make code slow because of cross-filters.

4

Thank for the response @BrianJ & @AntrikshSharma

What additional info would you like to get.

The TXN_ID is the lowest level of granularity. What other method exists for tackling such a query besides using an EXCEPT?

5

@Lazola,

Particularly when it comes to optimization questions, it’s highly beneficial to provide your PBIX file so we can test alternate approaches for performance.

  • Brian
7

@Lazola Try to write your code as shown below, might improve performance in your orignal data.

#DroppedDeals = 
VAR dC = [#DtC]
VAR dP = [#DtP]
VAR MinDate =
    MIN ( 'Date'[PrevBusinessDay] )
VAR MaxDate =
    MAX ( 'Date'[PrevBusinessDay] )
RETURN
    COUNTROWS (
        EXCEPT (
            CALCULATETABLE (
                VALUES ( 'Green Model'[TXN_IDs] ),
                'Date'[PrevBusinessDay] = MinDate,
                REMOVEFILTERS ( 'Date' )
            ),
            CALCULATETABLE (
                VALUES ( 'Green Model'[TXN_IDs] ),
                'Date'[PrevBusinessDay] = MaxDate,
                REMOVEFILTERS ( 'Date' )
            )
        )
    )

.

#DtP = 
VAR dC = [#DtC]
RETURN
    CALCULATE (
        MAX ( 'Date'[PrevBusinessDay] ),
        KEEPFILTERS ( 'Date'[PrevBusinessDay] < dC ),
        ALLSELECTED ( 'Date' )
    )
3 Likes
8

Thank you very much @AntrikshSharma , I will try all the suggestions.