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

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

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks for the replies.

       

      Both column are whole numbers, see screen shots.

       

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

     

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      sdjensen
      Solution Sage
      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]
)