cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

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
)
1 ACCEPTED SOLUTION
Super User

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]
)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

4 REPLIES 4
Super User

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]
)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Frequent Visitor

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

Super User

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.)

Frequent Visitor

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