See if value from Table A exists in table B via measure

[Slavi]

Hello PBI comminuty,

I am trying to find out whether a values from Column A Table A exist in Column B table B. 

I have manged to do this via calculated columns but because I am now using PBI datasets I cannot use calculated columns, only measures.

How can I do this?

Sample of my data from the two tables:

Table A:

Project Name	Duration	Owner	Description
ABC	2 days	Michael	-----
ABC	3 days	Owen	-----
ABB	1 day	Richard	-----
ABB	2 days	Silvia	-----
ABD	1 day	Neal	-----

Table B:

Project Name	Location	Building
ABC	Germany	1A
ABB	Germany	1B

Based on the data above the measure should bring back a number 1 for projects ABC and ABB because they appear in both Table A and table B, and 0 for project ABD because it is missing in Table B. 

Thank you!

[latimeria · Accepted solution]

@Slavi ,

If you only need to check whether there is a value, you can use the sql  equivalent of exist, not isempty

Exist in TableB =

VAR CurrentProject = SELECTEDVALUE(TableA[Project Name] )

RETURN

INT( CALCULATE( NOT ISEMPTY(TableB), TableB[Project Name] = CurrentProject ) )

[Slavi]

Thank you for the reply @latimeria , indeed it works for my case

[Slavi]

Hello @AlB and thanks for your response,

Unfortunatelly, in my case the Tranform Data option that the person used in the video is disabled and I cannot create the local model.

[Anonymous]

Hello

Try this

Mesure =
VAR proj =
SELECTEDVALUE ( 'Table A'[Project Name] )
VAR _step1 =
FILTER (
SUMMARIZE ( 'Table B', 'Table B'[Project Name] ),
'Table B'[Project Name] = proj
)
VAR result =
IF ( COUNTROWS ( _step1 ) > 0, COUNTROWS ( _step1 ), 0 )
RETURN
result

[AlB]

Hi @Slavi 

HAve you tried adding a local model to crete the calculated column?  Check this out:

https://youtu.be/g-nRxDVt3To?t=166

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.