Result if only multi values match

[Anonymous]

Hi Experts, 

I'm stuck! 

I have the data below, and need 'Result' column to show "yes" if,

Scode = BBB,

& IF icode matches another cell in icode &,

if Lcode matches both rows, &,

if Ocode on a matching row  = anything but 0. 

I also want 'Result 2' column to show the Ocode that matches on all 'Result' comlum "yes"... 

I have shown the results below...  

I hope this is possible!  Thanks in advance  PBI experts. 

Scode	icode	Lcode	Ocode	Result	Result 2
BBB	X	ABC	0	yes	123
BBB	C	ABC	0	yes	123
BBB	V	ABC	0	no	0
BBB	B	ABC	0	no	0
BBB	X	ABC	123	yes	123
BBB	C	ABC	123	yes	123
BBB	V	ABC	0	no	0
BBB	B	ABC	0	no	0
BBB	X	CDE	0	no	0
BBB	C	CDE	0	no	0
BBB	V	CDE	0	yes	456
BBB	B	CDE	0	yes	456
BBB	X	CDE	0	no	0
BBB	C	CDE	0	no	0
BBB	V	CDE	456	yes	456
BBB	B	CDE	456	yes	456

[d_gosbell · Accepted solution]

I'm assuming that you want to calculate this as a calculated column, not a measure as from what I can see it probably only makes sense at the row level. If this is correct I think the following 2 expressions should work

Result Column = 
VAR _currentIcode = Table1[icode]
var _currentLcode = Table1[Lcode]
VAR _table = FILTER(Table1, Table1[icode] = _currentIcode && Table1[Lcode] = _currentLcode && Table1[Ocode] <> 0)
return if( COUNTROWS(_table) > 0, "yes", "no")

Result2 Column = 
VAR _currentIcode = Table1[icode]
var _currentLcode = Table1[Lcode]
VAR _table = FILTER(Table1, Table1[icode] = _currentIcode && Table1[Lcode] = _currentLcode && Table1[Ocode] <> 0)
return CONCATENATEX( _table, [Ocode], ",")

[Anonymous]

Hi @d_gosbell  - this solution works perfectly.   A huge thank you for your time!