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 am currently trying to do something very simple.
I want to create a new column, such that it is the same as another column BUT where the 'other' column is blank it performs a lookupvalue function to fill it.
Example below:
Table ONE
A | B |
key1 | hi |
key2 | bye |
key3 | |
key4 | ok |
Table TWO
Key | Value |
key1 | hi |
key2 | bye |
key3 | so |
key4 | ok |
I want Table ONE to become:
A | B | C |
key1 | hi | hi |
key2 | bye | bye |
key3 | so | |
key4 | ok | ok |
I have written this code:
C = IF( isblank( `Table ONE`[B]), lookupvalue( `Table TWO`[Value],`Table TWO`[Key], `Table ONE`[A]), `Table ONE`[B])
But for some mysterious reason it doesn't work and Column C becomes exactly the same as Column B.
Anyone have a ny ideas would be appreciated.
Solved! Go to Solution.
Could be a couple of things. Looks like you are using the alternate result parameter for LOOKUPVALUE that makes the value your B column in the even of one of two things. Either LOOKUPVALUE is not finding a single row or LOOKUPVALUE is finding no rows.
You can find out by using COUNTROWS with a FILTER that mimics your LOOKUPVALUE search. That will tell you if you are getting no rows or multiple rows. You could use a MAXX(FILTER(TWO,TWO[Key]=ONE[A]),TWO[Value]) if you are getting multiple rows. Or MINX. If you are getting no rows then perhaps one of your key's has a space at the end or something like that.
You can try, a New Column in Table
new column Table 1= MAxx(filter(Table2,table1[A] =table2[key]),table2[Value])
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
You can try, a New Column in Table
new column Table 1= MAxx(filter(Table2,table1[A] =table2[key]),table2[Value])
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
Could be a couple of things. Looks like you are using the alternate result parameter for LOOKUPVALUE that makes the value your B column in the even of one of two things. Either LOOKUPVALUE is not finding a single row or LOOKUPVALUE is finding no rows.
You can find out by using COUNTROWS with a FILTER that mimics your LOOKUPVALUE search. That will tell you if you are getting no rows or multiple rows. You could use a MAXX(FILTER(TWO,TWO[Key]=ONE[A]),TWO[Value]) if you are getting multiple rows. Or MINX. If you are getting no rows then perhaps one of your key's has a space at the end or something like that.
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |