Forum Discussion
Retrieve value from another table without unique id
I have a pretty large table that has a rough layout similar to this:
I want to create a column in that table using values from a table that has a rough layout similar to this:
However, the tables do not have a unique id. I would just make a unique id if table 1 was that small but it is much larger. I simply want to assign a count value from table 2 into table 1 based on the year and group #. So the values will be repeated in table 1. I would like a result similar to this:
How do I go about doing this? I've tried using LOOKUPVALUE in a calculated column as well as in a measure. I am using DirectQuery but when I try to enable “Allow unrestricted measures in Direct Query Mode," there is no option avilable to me. Others also seem to be encountering this issue: https://community.powerbi.com/t5/Desktop/Lookupvalue-function-not-appearing-Direct-Query-mode/td-p/454407
Thanks in advance!
Hi Anonymous ,
Here is a suggestion:
NewColumn = CONCATENATE( Table[Year], Table[Group] )Create this column in both tables.
You should then be able to join these tables with Many to One Cardinality.
Let me know how it goes.Cheers,
Robin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
Here is a suggestion:
NewColumn = CONCATENATE( Table[Year], Table[Group] )Create this column in both tables.
You should then be able to join these tables with Many to One Cardinality.
Let me know how it goes.Cheers,
Robin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous ,
We can create a measure as below.
Column = CALCULATE ( MAX ( 'Table'[count] ), FILTER ( ALL ( 'Table' ), 'Table'[group] = Table1[group] && 'Table'[Year] = Table1[Year] ) )
