Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

DAX - IF Format

Hi All,   I am trying to do a SUM on column [Total Amount] when column [Promotion_id] is blank AND column [order_order_scr] = 1   My DAX for new column is;   NoOfferTotal = IF(AND(FORMAT([order...
Need Help
  • sdjensen's avatar
    sdjensen
    9 years ago

    v-jiascu-msft is right and it's a better solution in case that promition_id is actually "" in some cases.

     

    revised codes:

    measure = 
CALCULATE(
	SUM('POS Data 1'[Total Amount]),
	'POS Data 1'[promotion_id] = BLANK(),
	'POS Data 1'[order_order_src] = 1 
)

column =
IF( 
    AND( 
        'POS Data 1'[promotion_id] = BLANK(),
        'POS Data 1'[order_order_src] = 1
    ),
	'POS Data 1'[Total Amount]
)
sdjensen's avatar
sdjensen
Solution Sage
9 years ago

Hi Anonymous,

 

You can calculate this as a measure with this DAX code. I would however prefer to make the calculation a measure instead of a column unless you for some reason really need this as a column in your table.

 

NoOfferTotal = 
CALCULATE(
	SUM('POS Data 1'[Total Amount]),
	FORMAT( 'POS Data 1'[promotion_id], "String" ) = "",
	'POS Data 1'[order_order_src] = 1 
)

 

If you really need it as a column - this code should do the trick:

IF( 
    AND( 
        FORMAT( 'POS Data 1'[promotion_id]; "String" ) = "",
        'POS Data 1'[order_order_src] = 1
    ),
	'POS Data 1'[Total Amount]
)