Forum Discussion

C4L84's avatar
C4L84
Advocate II
4 years ago

Addcolumn with if statement

Hi community,

 

I'm looking for some help with an ADDCOLUMN expression.

 

I need to add a new column called "RevenueType" in the BookingStatus table which groups BookingCodes A, R, H and CX as "Confirmed revenue" with all remaining codes as "Provisional".

 

Here is a pbix example.

 

I've got as far as creating the if statement: 

RevenueType = IF(MIN('Booking statuses'[BookingCode]) in {"A","R","H","CX"}, "Confirmed revenue", "Provisional")
 
But can't figure out how to add this column to the table as I am getting and error that reads "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
 

 

It should be noted that this has to be a DAX expression and the data cannot be modified in the query editor.

 

I'm hoping someone in the community can help as I've been stuck on this for some time!

DAX

8 Replies

  • Vasuno1's avatar
    Vasuno1
    Frequent Visitor
    3 years ago

    I am not sure that you need to use the addcolumn function for this but if you really need to let me know.

     

    I achieved what you are looking for by adding a new column as shown below:

     

    Then I inputted the following in the formula bar:

    RevenueType = IF('Booking statuses'[BookingCode] in {"A","R","H","CX"},"Confirmed Revenue", "Provisional")
    RevenueType = IF('Booking statuses'[BookingCode] in {"A","R","H","CX"},"Confirmed Revenue", "Provisional")
     
    See result below:

     

     

    • C4L84's avatar
      C4L84
      Advocate II
      4 years ago

      Thanks for replying SpartaBI 

       

      I need to add a new column to the BookingStatus table using DAX, which uses the IF statement.