cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Deborah1982
New Member

DAX formula where should I insert this?

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: DAX formula where should I insert this?

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: DAX formula where should I insert this?

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

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Microsoft v-yulgu-msft
Microsoft

Re: DAX formula where should I insert this?

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors