Optimize existing DAX measure

[IzuruWi]

Hi All, Anyone can help me to optimize the below DAX Measure? It takes more time and affects the visualization loading. I have copied the text in the DAX measure below.

Thanks you very much.

MEASURE FACT_CONSOL_BALANCE_OL[Measure 4] =
SWITCH (
TRUE (),
CONTAINS (
DIM_ANALYTIC_STRUCT_ACCOUNT,
DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)], "1 - CURRENT ASSETS"
), SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ),
CONTAINS (
DIM_ANALYTIC_STRUCT_ACCOUNT,
DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)], "2 - NON - CURRENT ASSETS"
), SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ),
SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ) * -1
)

[ImkeF · Accepted solution]

Hi @IzuruWi ,
I am not used to using the contains function, but everything else looks like it should load fairly fast.
In your case, I would recommed to add a "Sign"-column to your Accounts-table that shows either 1 or -1 according to the logic in your measure code.
Then you can just do a:
M =
SUMX (
VALUES ( DIM_ANALYTIC_STRUCT_ACCOUNT[YourNewSignColumn] ),
CALCULATE ( SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ) ) * DIM_ANALYTIC_STRUCT_ACCOUNT[YourNewSignColumn]
)

[IzuruWi]

I have managed to do it in a little different way.. Thank you very much for the support @ImkeF 

[AlexisOlson]

Another possibility is to calculate the pieces separately.

CALCULATE (
    SUM ( FACT_OL[BALANCE] ),
    "1 - CURRENT ASSETS" IN VALUES ( DIM_ACCOUNT[STRUCTURE] )
)
-
CALCULATE (
    SUM ( FACT_OL[BALANCE] ),
    "2 - NON - CURRENT ASSETS" IN VALUES ( DIM_ACCOUNT[STRUCTURE] )
)

(Table and column names shortened for readability.)

[IzuruWi]

Thank you very much @AlexisOlson. For the time being I have done it little differently. However managed get it done.