Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Lookup value from one table from another

I have a large table with one of the columns as 'CID' and there are multiple rows in this table with the same CID value.

There is another table with distinct CID codes that I need to match the previous table against.

 

So, I need to add a column to the large table that says CIDmatchs with logic being, if there is a match with the lookup table value; set it to 1 else 0 so I can count all the 1s together.

 

Can someone please help me with the logic for DAX or M any any method I can use to resolve this??

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago
    CID Flag = IF(
	IFERROR(
		LOOKUPVALUE(
			'CID Table'[CID],
			'CID Table'[CID],
			'Large Table'[CID]
		),
		0
	) = 0,
	0,
	1
)

    Make that a column in your large table.  You might need to rename the tables names i've used, but hope you get the idea.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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.