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 all,
In a calculated column, I'm trying to insert a looked-up value from an unrelated table using a "contains" text comparison.
I understand how to use FIND to test that one text string contains a second. I can't figure out how to obtain the row and subsequent value from the second table using that logical test. I tried using FILTER, but I'm stuck getting at the actual results of that filter.
The first table has a Description column containing a long text value, e.g. "Whole Foods DBCA 185626".
The second table has two columns, a Short Description text field ("Whole Foods") and a Sub-Category field ("Grocery") which is the value I want inserted into the first table.
So, essentially: Find the row in Table 2 where Table1[Description] contains Table2[Short Description] and then return the Sub-Category value from that row.
Thanks in advance for your help!
Solved! Go to Solution.
Try a new column like
New column in Table 1 = minx(filter(table2,search(Table2[Short Description],Table1[Description],1,0)>0),Table2[Sub-Category])
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
Try a new column like
New column in Table 1 = minx(filter(table2,search(Table2[Short Description],Table1[Description],1,0)>0),Table2[Sub-Category])
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
Thank you, that did it!
I was on the right track with the FILTER function, but I didn't know how to derive a scalar value from the resulting table. MINX seems to work fine, though I wonder if there are other ways.
Thank you!
Hi @Anonymous
It's likely the LOOKUPVALUE function can help dax.guide/lookupvalue/
Best regards,
Martyn
Thanks for the reply. I looked at LOOKUPVALUE previously, but as far as I can tell it does a straight value comparison (value1==value2) to find a match, and doesn't allow for the "text contains" comparison I need. Please correct me if this is wrong.
@Anonymous
Correct - LOOKUPVALUE will only return results if there's an exact match.
Maybe I misread your original post but it sounded like you knew what you were seaching for?!
Are you able to share some sample data so we can better understand the issue your facing?
Best regards,
Martyn
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |