Search code examples
performancepowerbidaxmeasure

Optimizing DAX Has X Amount or More in Time Frame

I am looking to speed up this calculation that takes a minute or two to load each time I add a different filter view on the page.

The base measure just calculates the total logins in a rolling 3 month which works as expected.

TotalLoginsIn90 =
CALCULATE (
    SUM ( [Logins] ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( Logins[Date] ), -3, MONTH )
)

I am now looking to identify in each month the amount of users that make over 3 logins in each month.

CustLoginIn90 =
CALCULATE (
    SUMX ( DISTINCT ( Logins[Customer Number)] ), 1 ),
    FILTER (
        VALUES ( Logins[Cust Customer Number (Numeric)] ),
        [TotalLoginsIn90] > 3
    )
)

or

CustLoginIn90 =
CALCULATE (
    DISTINCT ( Logins[Customer Number] ),
    FILTER (
        VALUES ( Logins[Cust Customer Number (Numeric)] ),
        [TotalLoginsIn90] > 3
    )
)

each of these takes its sweet time to calculate but ends up with the correct amount.

Is there any way to speed up these measures?

Thanks for the help!

Solution

  • I would recommend making the TotalLoginsIn90 as a calculated column instead of a measure so it only needs to be calculated once (per report refresh).

    TotalLoginsIn90 = 
CALCULATE (
    SUM ( [Logins] ),
    DATESINPERIOD (Logins[Date], Logins[Date], -3, MONTH)
)

    That also allows you to write a very simple condition for your CustLoginIn90 measure:

    CustLoginIn90 = SUMX(Logins, IF(Logins[TotalLogins90] > 3, 1, 0))