slow measure and need help

[Anonymous]

hello I need help with a measure that i am using in a coumn.  i have 120 GB available and it runs out of memory.  there is 20 million rows in the table 

here is the measure 

HLC =

VAR Active =

CALCULATE (

SUM ( Full_FACT_SALES[if active] ),

FILTER (

ALLEXCEPT ( Full_FACT_SALES, Full_FACT_SALES[Customer_ID] ),

Full_FACT_SALES[Transaction_DateTime]

<= MAX ( Full_FACT_SALES[Transaction_DateTime] )

)

)

VAR New =

CALCULATE (

SUM ( Full_FACT_SALES[if new] ),

FILTER (

ALLEXCEPT ( Full_FACT_SALES, Full_FACT_SALES[Customer_ID] ),

Full_FACT_SALES[Transaction_DateTime]

<= MAX ( Full_FACT_SALES[Transaction_DateTime] )

)

)

VAR Reactivated =

CALCULATE (

SUM ( Full_FACT_SALES[if Reactivated] ),

FILTER (

ALLEXCEPT ( Full_FACT_SALES, Full_FACT_SALES[Customer_ID] ),

Full_FACT_SALES[Transaction_DateTime]

<= MAX ( Full_FACT_SALES[Transaction_DateTime] )

)

)

VAR Risk =

CALCULATE (

SUM ( Full_FACT_SALES[if Risk] ),

FILTER (

ALLEXCEPT ( Full_FACT_SALES, Full_FACT_SALES[Customer_ID] ),

Full_FACT_SALES[Transaction_DateTime]

<= MAX ( Full_FACT_SALES[Transaction_DateTime] )

)

)

VAR Lapsed =

CALCULATE (

SUM ( Full_FACT_SALES[if Lapsed] ),

FILTER (

ALLEXCEPT ( Full_FACT_SALES, Full_FACT_SALES[Customer_ID] ),

Full_FACT_SALES[Transaction_DateTime]

<= MAX ( Full_FACT_SALES[Transaction_DateTime] )

)

)

RETURN

IF (

Active > 0,

"Active",

IF (

New > 0

&& Active = 0

&& Risk = 0

&& Lapsed = 0,

"New",

IF (

Risk > 0

&& New = 0

&& Active = 0,

"Risk",

IF (

Lapsed > 0

&& New = 0

&& Active = 0

&& Risk = 0,

"Lapsed",

IF (

New > 0

&& Lapsed > 0

&& Active = 0

&& Risk > 0,

"Reactivated",

IF (

New > 0

&& Lapsed > 0

&& Active = 0

&& Risk = 0,

"Reactivated",

IF ( New > 0 && Lapsed = 0 && Active = 0 && Risk > 0, "Reactivated", "" )

)

)

)

)

)

)

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Try formula as below:

HLC =
VAR T_DT =
    MAX ( Full_FACT_SALES[Transaction_DateTime] )
VAR Active =
    CALCULATE (
        SUM ( Full_FACT_SALES[if active] ),
        ALL ( Full_FACT_SALES[Transaction_DateTime] ),
        Full_FACT_SALES[Transaction_DateTime] <= T_DT
    )
VAR New =
    CALCULATE (
        SUM ( Full_FACT_SALES[if new] ),
        ALL ( Full_FACT_SALES[Transaction_DateTime] ),
        Full_FACT_SALES[Transaction_DateTime] <= T_DT
    )
VAR Reactivated =
    CALCULATE (
        SUM ( Full_FACT_SALES[if Reactivated] ),
        ALL ( Full_FACT_SALES[Transaction_DateTime] ),
        Full_FACT_SALES[Transaction_DateTime] <= T_DT
    )
VAR Risk =
    CALCULATE (
        SUM ( Full_FACT_SALES[if Risk] ),
        ALL ( Full_FACT_SALES[Transaction_DateTime] ),
        Full_FACT_SALES[Transaction_DateTime] <= T_DT
    )
VAR Lapsed =
    CALCULATE (
        SUM ( Full_FACT_SALES[if Lapsed] ),
        ALL ( Full_FACT_SALES[Transaction_DateTime] ),
        Full_FACT_SALES[Transaction_DateTime] <= T_DT
    )
RETURN
    IF (
        Active > 0,
        "Active",
        SWITCH (
            TRUE (),
            New > 0
                && Active = 0
                && Risk = 0
                && Lapsed = 0, "Risk",
            Lapsed > 0
                && New = 0
                && Active = 0
                && Risk = 0, "Lapsed",
            New > 0
                && Lapsed > 0
                && Active = 0
                && Risk > 0, "Reactivated",
            New > 0
                && Lapsed > 0
                && Active = 0
                && Risk = 0, "Reactivated",
            New > 0
                && Lapsed = 0
                && Active = 0
                && Risk > 0, "Reactivated",
            ""
        )
    )

Best Regards,
Community Support Team _ Eason

[Anonymous]

Switch is a good suggestion i will try that now.  

[Greg_Deckler]

Yeah, you should check out my Performance Tuning DAX series (4 parts) here: https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

Performance Tuning DAX is no joke, it is involved and not sure I can really help without having access to the data and being able to experiment. Your nested IF statements should be replaced with a SWITCH statement. Logic can likely be improved. You have a lot of repeated code that you should use variables for. You use the same FILTER over and over again, store that FILTER in a table variable and do a SUMX across it for each of your New, etc. variables.

Just a few thoughts.