Custom Column with Ifs/And statement

[Anonymous]

Hi - I'm new to BI and trying to figure out the best way to create the below  custom column in an existing report.

Looking to return 'accept' in the new column if each Col A/B/C contain any of the values provided in Col1/2/3, respectively. All rows not meeting this criteria would simply return 'other' in the new column.

So for each row, If ColA contains one of the values provided in Col1, AND ColB contains one of the values provided in Col2, AND ColC contains one of the values provided in Col3, then Return 'accept' in the custom colum

thank you!

	Report 1					Report 2
Col A	Col B	Col C	Custom Col		Col 1	Col 2	Col 3
5	orange	x	Pass		1	apple	x
2	apple	y	Accept		2	banana	y
19	kiwi	z	Pass		3	kiwi	z
5	banana	y	Accept		4
4	apple	z	Accept		5
1	kiwi	o	Pass
3	grape	p	Pass
2	grape	y	Accept

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

Measure = IF(MAX(Report1[Col A]) in FILTERS(Report2[Col 1])&&MAX(Report1[Col B]) in FILTERS(Report2[Col 2])&&MAX(Report1[Col C]) in FILTERS(Report2[Col 3]),"Accept","Pass")

And the result shows:

Hope this helps.

Best Regards,

Giotto Zhi