DAX formula where should I insert this?

[Deborah1982]

Hi,

I have created in a previous report an extra column, with a formula, if I now check the formula it's: 

"Company", each if Text.Contains([SO nr], "BMB") then "BMB" else if Text.Contains([SO nr], "HER") then "HERENTALS" else if Text.Contains([SO nr], "HZ") then "HEUSDEN-ZOLDER" else if Text.Contains([SO nr], "EBAY") then "EBAY" else if Text.StartsWith([SO nr], "SO") then "WEBSITE SALES" else "to be defined")

However if I add column in my new report, it gives an error, to not know why?

Is there a difference between formula in the column area, and query area?

Regards

Deborah

[v-yulgu-msft · Accepted solution]

Hi @Deborah1982,

To add an extra column using DAX code in Report View mode, you could try below formula. Suppose the table name is 'Table1' and the new column is named as 'Company'.

Company =
IF (
    NOT ( ISERROR ( SEARCH ( "BMB", Table1[SO nr] ) ) ),
    "BMB",
    IF (
        NOT ( ISERROR ( SEARCH ( "HER", Table1[SO nr] ) ) ),
        "HERENTALS",
        IF (
            NOT ( ISERROR ( SEARCH ( "HZ", Table1[SO nr] ) ) ),
            "HEUSDEN-ZOLDER",
            IF (
                NOT ( ISERROR ( SEARCH ( "EBAY", Table1[SO nr] ) ) ),
                "EBAY",
                IF (
                    NOT ( ISERROR ( SEARCH ( "SO", Table1[SO nr] ) ) ),
                    "WEBSITE SALES",
                    "to be defined"
                )
            )
        )
    )
)

Best regards,
Yuliana Gu

[TomMartens]

Hey,

there is a big difference between the formula that is (I would better say: can be) used in the column area or the query area.

To be absolutely sure that we are talking about the same:

The query area is the area where you get data from your source files / systems.

Within this area your Point/Click transformations are translated into a script/programming language called "M". The formula you are using in your post makes use of this language.

The column area, as you call it, allows you to add additional columns to existing tables (created through the query area), using the query language DAX.

Both languages are totally different, syntax and functions, because both have to solve some completely different tasks.

Unfortunately it's possible to add columns using M (query area - the tool is often called Query Editor, or still Power Query, the name of the Excel Add-in until Excel 2013), and it's also possible to add columns using DAX - the question you have to answer, where to go.

The answer, as always, it depends.

Here are links to the official DAX functions that come to my mind looking at your statement

• IF function
• SWITCH function

Personally, I tend to create columns using M, as long as the value can be derived for each row separately.

Hope this helps somehow

Regards

Tom