Forum Discussion

kavitaTickoo's avatar
kavitaTickoo
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago
Solved

How to fetch a specific string of data from a column using dax code?

I want to fetch the campaign string from the column Tags in my table if the value in the title column also contains campaign word and put it into another column. I want to search for the word "Campaign" in the tags column (so that in future if new campaign tags are added I won't have to update my code) and if it is present, I want to fetch just the campaign string present and no other tags. I can't use the split column using delimiter because not every row has this tag. 

 

eg:

 

Title                                                   Tags                                                          CampaignTagsWanted

Campaign Product A                      Campaign Jan, XYZ Tag                               Campaign Jan

Campaign Product B                      Campaign Feb, ABC Tag                              Campaign Feb

Campaign Product C                      Mar, LMN Tag                            

Product D                                       ABCD Tag, LMN Tag

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi kavitaTickoo 

    Could you accept to use Edit queries in Power BI?

    I make a test as below

    Please download my pbix file, open Edit queries,

    click on the setp and the icon on right to see details for each step.

     

    Steps:

    In Edit queries,

    1. Add column->add index column from 1

    2. copy Table 1, paste to get Table 2

    3. in Table2, click on column "Tags ", Transform->Split column->By delimiter "comma"

    4. select "Tags .1" and "Tags .2", Transform->"Unpovit columns"

    5. click on "Value" column, Transform->Format->trim

    6. split column "Value" by delimeter 'space"

    7. Add  column->conditional column

    8.  click on "Value.1" and "Value.2" columns, Add column->merge columns, thus get a column "Merged"

    9.  filter rows: remove empty for "condition1",

    10. select "Title", "Index","Merged", then Home->remove other columns

    11. go to Table1, merge queries (Table1 and Table2) based on "index" column

    Expand column "merged" from Table2

     

    Best Regards
    Maggie

     

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

9 Replies

  • Gordonlilj's avatar
    Gordonlilj
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Hi,

     

    Does this give you the result you are after?

    CampaignTagsWanted = IF((SEARCH("Campaign", [Tags], 1, 0) > 0), LEFT([Tags],(FIND(",",[Tags],1,1)-1)))
      • Gordonlilj's avatar
        Gordonlilj
        Icon for Solution Sage rankSolution Sage
        7 years ago

        Is it only returning blank rows?

        And is it possible for you to post some more sample data

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi kavitaTickoo 

    Could you accept to use Edit queries in Power BI?

    I make a test as below

    Please download my pbix file, open Edit queries,

    click on the setp and the icon on right to see details for each step.

     

    Steps:

    In Edit queries,

    1. Add column->add index column from 1

    2. copy Table 1, paste to get Table 2

    3. in Table2, click on column "Tags ", Transform->Split column->By delimiter "comma"

    4. select "Tags .1" and "Tags .2", Transform->"Unpovit columns"

    5. click on "Value" column, Transform->Format->trim

    6. split column "Value" by delimeter 'space"

    7. Add  column->conditional column

    8.  click on "Value.1" and "Value.2" columns, Add column->merge columns, thus get a column "Merged"

    9.  filter rows: remove empty for "condition1",

    10. select "Title", "Index","Merged", then Home->remove other columns

    11. go to Table1, merge queries (Table1 and Table2) based on "index" column

    Expand column "merged" from Table2

     

    Best Regards
    Maggie

     

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