Forum Discussion

Gambrinus's avatar
Gambrinus
Frequent Visitor
8 years ago

populate multiple columns

Hi,

 

I have text data that might look like this:

 

A fruit that grow in bunches are banana

Red Delicious apples grow on trees

Carrots grow in the ground

A summer vegtable is corn

 

I've added data to a custom column that extract the food from the text using something like this:

 

 

Fruit=CONCATENATEX (
    FILTER ( Categories, SEARCH ( Categories[Fruits], [Text Data],, 0 ) > 0 ),
    Categories[Fruits],
    ""
)
Return

VAR
Vegetable=CONCATENATEX (
    FILTER ( Categories, SEARCH ( Categories[Vegetables], [Text Data],, 0 ) > 0 ),
    Categories[Vegetable],
    ""
)
Return

Fruit&Vegetable

 

This works nicely. However, I want to populate another column with the literal "Fruit" or "Vegetable" depending on my variables.

 

I know I could use the formula again on the other column, but that seems inefficient. I could also append the correct word (eg if trim(Fruit)<>"",Fruit&"Fruit")  with a delimiter inbetween and then extract the data in the other column. However, I'm trying to figure out if I can populate the column I want directly.

 

I tried this with no luck:

if trim(Fruit)<>"","Fruit","Vegetable")

Any help is appreciated.

 

Need Help

4 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi  Gambrinus,

     

    If I understand your requirement correctly that you want to reference the var fruit when you create another calculated column?

     

    If it is, it seems that is not supported in Power BI currently. 

     

    If you don't want to create the var fruit for each time when you create the calculated column, you could create the fruit as a calculated column, then you could reference it when you create another calculated column.

     

    Best Regards,

    Cherry

    • Gambrinus's avatar
      Gambrinus
      Frequent Visitor
      8 years ago

      Thanks for the reply Cherry.

       

      What I want, is once I've detemined the type of food in ColumnJ, to populate another column (ColumnX) with a literal for the type of food.

       

      In my two-food-type example, either Var Fruit or Var Vegetable will be populated. I'd like to accomplish this:

       

      If(trim(Fruit)<>"",[ColumnX]="This is a Fruit",If(trim(Vegetable)<>"",[ColumnX]="This is a Vegetable","Unknown"))

       

      So if the Var Fruit has a value, then ColumnX will contain the literal test, otherwise, if Var Vegetable has a value, ColumnX will have different literal text, otherwise ColumnX will have "Unknown"

       

      Thank you.

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        8 years ago

        Hi Gambrinus,

         

        Try this formula below.

         

        Column 2 =
VAR fruit =
    LOOKUPVALUE ( 'table'[Fruits], 'table'[Text Data], 'table'[Text Data] )
VAR vege =
    LOOKUPVALUE ( 'table'[Vegetables], 'table'[Text Data], 'table'[Text Data] )
RETURN
    IF (
        vege <> "",
        "This is a vegetable",
        IF ( fruit <> "", "this is a fruit", "Unknown" )
    )

        Here is the output.

         

         

        For more details, you could have a reference of the attachment.

         

        Best  Regards,

        Cherry