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 previ...
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.

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago
      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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.

         

         

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Anonymous,

     

    I asume the large table have the sample data

     

    Now you need to count the distinct value for the CIDs that have CID Flag=1, right?

    If that is the case, please try to create a measure using the DAX below.
    Measure = CALCULATE(DISTINCTCOUNT(Table3[CID]),FILTER(Table3,Table3[CID flag]=1))

     

    Regards,

    Charlie Liao

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks Charlie. I could get the distinct counts using your logic.