Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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.
Hi @Gambrinus,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |