Forum Discussion
Optimize existing DAX measure
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.
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
)
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]
)
4 Replies
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]
)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.)
Thank you very much AlexisOlson. For the time being I have done it little differently. However managed get it done.
