Check if value exists in another table based on a specific category

[Emanuel]

I have 2 tables Problem table and Problem workinfo table and I need the following output:

Problem table		Problem work info table		OUTPUT
Problem ID		Problem ID	Type		Problem ID	Does it have a "Resolution Communication" work info type?
PBI000030128704		PBI000030128704	General Information		PBI000030128704	no
PBI000030134102		PBI000030134102	General Information		PBI000030134102	no
PBI000030138400		PBI000030138400	General Information		PBI000030138400	no
PBI000030138931		PBI000030138931	General Information		PBI000030138931	yes
PBI000030148328		PBI000030138931	General Information		PBI000030148328	no
PBI000030148739		PBI000030138931	General Information		PBI000030148739	no
PBI000030148942		PBI000030138931	General Information		PBI000030148942	no
PBI000030149220		PBI000030138931	General Information		PBI000030149220	no
PBI000030150803		PBI000030138931	General Information		PBI000030150803	yes
		PBI000030138931	General Information
		PBI000030138931	Resolution Communications
		PBI000030148328	General Information
		PBI000030148328	General Information
		PBI000030148739	General Information
		PBI000030148942	General Information
		PBI000030148942	General Information
		PBI000030148942	General Information
		PBI000030148942	General Information
		PBI000030149220	General Information
		PBI000030150803	General Information
		PBI000030150803	General Information
		PBI000030150803	General Information
		PBI000030150803	Resolution Communications

There is a Many-to-one cardinality with both ways cross filter active for these tables. 

I am haveing dificuties finding a solution to achiev the above output.

Thanks for the help in advance

[v-jianboli-msft · Accepted solution]

Hi @Emanuel ,

Approve with @Anonymous .

Here's a way to achieve it via Measure:

Does it have a "Resolution Communication" work info type? = IF("Resolution Communications" in SELECTCOLUMNS('Problem work info table',"Type",[Type]),"yes","no")

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Emanuel]

Thanks Li for your solution.

How can I include this measure in a larger calculation?
So, what I need is to create a measure/column for KPI that has to contain the following logic: If(

Does it have a "Resolution Communication" work info type?=yes and 

Report_tgt_date<=Action Date then KPI is met else is not.

I've added 2 colums to your solution to help with the outcome (I don't know why is not letting me to insert the bi file itself)

[Anonymous]

Hi

Please follow the below steps.

1. Create a conditional column as a flag.

2. Now duplicate this table.

3. Use group by function 

4 Create conditional column 

Output:

Best Regards,

Shreya Mukkawar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.