Lookup value from one table from another

[Anonymous]

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??

[Anonymous]

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]

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.

[v-caliao-msft]

@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]

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

[Anonymous]

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]

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?

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

Thanks..  @Anonymous

However, it is not allowing me to keep the third arguement of lookuptable function to use a table column, but, only measures of either tables. And, I tried in both ways.. for the larger as well as the smaller table.

Is there something that I might be missing?

[Anonymous · Accepted solution]

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]

@Anonymous

Thanks a lot Ross... It worked this time.. I guess I was trying to create a measure instead of a column. 😛

Really appreciate your help!