Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DistinctCount Calculation going slow upon 7 day average calculation

Hello all,

 

I need some help with trying to speed up this calculation. The main performance issue is that it takes my dashboard about 1 minute + to reload visuals which make it hard when presenting to exec's (As we filter dates a lot, it really is inconvinient) 

Here's the basis of what I'm trying to accomplish:
I have a transaction dataset that is granular to each specific transaction. I have CustomerID along with a "FinalApproval" column that indicates 1 for successful, 0 for failed. With this, I am calculating a specific metric that shows "DistinctCustomerApprovals"/"DistinctCustomers". This can be calculated pretty quickly, but it's as soon as I do a 7 day average calculation that it increases the time x10 fold (which makes sense, but I'm sure there's way to optimize this that I'm not aware of)


Current Calculations: 

7Day Average calculation:

 

7d Avg. Approval % = 
VAR NumDays = 7
VAR RollingSum =
    CALCULATE(
        [DistinctApprovalRate],
        DATESINPERIOD('Calendar Table'[Date],LASTDATE('Calendar Table'[Date]),-NumDays,DAY)
    )
RETURN
RollingSum

 

 

Distinct Approval Rate:

 

DistinctApprovalRate = 
    CALCULATE(
            DISTINCTCOUNT(Transactions[customerId]), 
                Transactions[FinalApproval]=1)/DISTINCTCOUNT(Transactions[customerId])

 

 

After some research, I tried a couple different methods as I have deemed the issue being mostly related to the Counts (I have another metric that gives total ApprovalRate which is ApprovedTransactions/TotalTransactions and I am able to throw it into that 7 day average formula no problem and takes about 2 seconds to refresh all visuals)

Method 1:
Split the 2 counts into their own measures and then have DistinctApprovalRate measure divide the 2. 

Method2:
Use COUNTROWS() instead of DISTINCTCOUNT(). This seemed to have made it slower in the 7 day average? 

 

Distinct Customers = 
CALCULATE(
        COUNTROWS(VALUES(AuthRate[customerId])), AuthRate
)

////////
ApprovedDistinctCustomers = 
CALCULATE(
        COUNTROWS(VALUES(AuthRate[customerId])), AuthRate[FinalApproval]=1
)
////////
DistinctApprovalRate = [ApprovedDistinctCustomers]/[Distinct Customers]

 

 

Hopefully this all makes sense. To give an idea of the scale, this is a 13mil row dataset. I know it's large so there may not be anything that I can do, but anything would help!

Thank you 

Need Help

3 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    try like this:

     

    DistinctApprovalRate = 
 VAR _Dis = SUMX(VALUES(Transactions[customerId]),1)
RETURN
    CALCULATE(
            _Dis, 
                Transactions[FinalApproval]=1)/_Dis)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hmm, Tried this a couple times in a couple different measures to differientiate it. The issue that I run across with using this method is that the CALCULATE(_Dis, Transactions[FinalApproval] = 1) Does not actually provide any filter on the SUMX Variable

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Update:

      Looks like DAX does not do any filters on any Variables. I tried the similar solution that I had with COUNTROWS() and it did not provide the filtering

       

      ApprovedDistinctCustomers2 = 
VAR _Dis = COUNTROWS(VALUES(AuthRate[CustomerID]))
RETURN
    CALCULATE(
        _Dis, AuthRate[FinalApproval] = 1)

       

      Edit: After looking at Variables in DAX a bit further, it looks like they are all constant/static variables that cannot be changed once created. Makes sense from a functional programming perspective but I've been told by my dev team that dax likes variables for speeds so that sucks but makes sense why, because it's all static so ofcourse it's fast lol.