Forum Discussion
Optimize existing DAX measure
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]
)
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.
