topic Re: Check if value exists in another table based on a specific category in DAX Commands and Tips

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

Emanuel — Mon, 14 Nov 2022 10:33:55 GMT

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

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

Anonymous — Tue, 15 Nov 2022 07:40:45 GMT

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.

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

v-jianboli-msft — Tue, 15 Nov 2022 07:10:38 GMT

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.

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

Emanuel — Thu, 24 Nov 2022 10:22:55 GMT

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)