topic Re: Addcolumn with if statement in DAX Commands and Tips

Addcolumn with if statement

C4L84 — Tue, 02 Aug 2022 09:17:50 GMT

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!

Re: Addcolumn with if statement

SpartaBI — Tue, 02 Aug 2022 12:21:51 GMT

C4L84 do you mean like this?

RevenueType = IF('Booking statuses'[BookingCode] in {"A","R","H","CX"}, "Confirmed revenue", "Provisional")

Here is a link to download the file with the solution:
Addcolumn with if statement 2022-08-02.pbix

Showcase Report – Contoso By SpartaBI

Re: Addcolumn with if statement

C4L84 — Tue, 02 Aug 2022 13:13:56 GMT

Thanks for replying SpartaBI

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

Re: Addcolumn with if statement

SpartaBI — Tue, 02 Aug 2022 13:26:02 GMT

C4L84 i'm confused 😁 isn't it what I sent you?

Re: Addcolumn with if statement

C4L84 — Tue, 02 Aug 2022 13:28:32 GMT

SpartaBI I need to specifically use the ADDCOLUMN funtion, something like this:

RevenueType =

ADDCOLUMNS('Booking statuses',"RevenueType",

IF(MIN('Booking statuses'[BookingCode]) in {"A","R","H","CX"}, "Confirmed revenue", "Provisional")

)

But I am getting this error message:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Re: Addcolumn with if statement

Ivn_rmr — Sun, 02 Jul 2023 03:50:25 GMT

Hi, I know it has been a while since you posted your question. Did you come across with any solution? I am having the same issue.

Re: Addcolumn with if statement

tamerj1 — Sun, 02 Jul 2023 09:33:21 GMT

Hi Ivn_rmr
Please explain your issue perhaps I can help.

With regard to this particular question, I can see that SpartaBI already provided a workable solution. However, the request of using ADDCOLUMNS function as per C4L84 is not clear as ADDCOLUMNS returns a table not a scalar value.

Re: Addcolumn with if statement

Vasuno1 — Tue, 11 Jul 2023 20:15:27 GMT

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

See result below:

Re: Addcolumn with if statement

Vasuno1 — Wed, 12 Jul 2023 13:25:00 GMT

C4L84 - Sorry I didn't realize that you reiterated your absolute need to use the ADDCOLUMNS function (nor did I notice that someone already posted the exact solution I did 😞)

C4L84 / Ivn_rmr - I used the below formula to create the table using the ADDCOLUMNS function:

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