Forum Discussion
Convert SQL case statement to DAX
Hi all,
I need some help with convert the below CASE statement to DAX query as a measure. Which should be able to execute in it's optimal perfomance state.
** I have mentioned the columns in the PBIX file for easier reference.
CASE WHEN
DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)]
IN ('1 - CURRENT ASSETS','2 - NON - CURRENT ASSETS')
THEN SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] )
ELSE SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ) * -1 END
NewMeasure=IF(MAX(DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)]) IN {"1 - CURRENT ASSETS","2 - NON - CURRENT ASSETS"},SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ),SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ) * -1)
or
NewMeasure=IF(SELECTEDVALUE(DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)]) IN {"1 - CURRENT ASSETS","2 - NON - CURRENT ASSETS"},SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ),SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ) * -1)
6 Replies
NewTable=ADDCOLUMNS(VALUES(DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)]),"Total",IF(DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)] IN {'1 - CURRENT ASSETS','2 - NON - CURRENT ASSETS'},CALCULATE(SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] )),CALCULATE(SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] )) * -1))
wdx223_Daniel Thank you very much for the quick response. Actually I need this to be used in a measure. So, how it should be changed? Sorry to bother you.
NewMeasure=IF(SELECTEDVALUE(DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)]) IN {'1 - CURRENT ASSETS','2 - NON - CURRENT ASSETS'},SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ),SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ) * -1)
