DAX - IF Format

[Anonymous]

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_order_src] = 1,"String"), 'POS Data 1'[promotion_id] = " "),SUM('POS Data 1'[Total Amount]),0)

This errors and DAX comparison operations do not support values of string and integer. 

Any help would be appreicated.

[v-jiascu-msft]

@Anonymous

Hi,

It seems that the answer is there. So I just give a suggestion that you can replace "" with BLANK(). "Format" isn't  needed.

Best Regards!

Dale

[sdjensen · Accepted solution]

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

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

[Phil_Seamark]

Hi @Anonymous

Can you please show a snapshot of some of the data so we can see what kind of values are in each column?

That will help reformat the query.

I'm mostly interested in the [order_order_src] column

[Anonymous]

Thanks for the replies.

Both column are whole numbers, see screen shots.