Forum Discussion
Lookup value from one table from another
Hi Anonymous, sounds to me like you have selected "Add Measure" instead of "Add Column"?
Just check that you have used "Add Column" and done so in the table that will hold the CID Flag. In this case we are adding it to the CID Table.
Thanks a lot for the solution. And, it has definitely solved part of my issue.
I forgot to add another problem to it.
So, the counts calculated by the 'CID Flag' is also counting the duplicates since there are rows with same CID values in the larger table. How should I make sure that the Flag only counts the distinct rows from the larger table?
Thanks in advance.
Try the same formula, except do it from the 'CID Table' to the 'Large Table' instead (in reverse). Lookup works on multiple rows, as long as each row would return the same value.
I tried to replace the arguments and put it as :
CID Flag = IF( IFERROR( LOOKUPVALUE( 'Large Table'[CID], 'Large Table'[CID], 'CID Table'[CID]), 0 ) = 0, 0, 1 )
But, this is not working as the LOOKUPVALUE function is not allowing me to use 'CID Table'[CID] as the thrid argument at all. I can only see the columns of the Large Table for the third argument.
However, it is running when the previous query was used :
CID Flag = IF( IFERROR( LOOKUPVALUE( 'CID Table'[CID], 'CID Table'[CID], 'Large Table'[CID] ), 0 ) = 0, 0, 1 )
Could you please assist me here?
Also, it seem the third argument needs to be a single value so, when I try using a column of another table, its just showing me the option to choose another measure as the third argument.
Make the CID Flag a column in the other table, not the large table. Thats the only way to reverse it correctly. You will still be able to include it in your reports.