cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TRIPPR Frequent Visitor
Frequent Visitor

Result if only multi values match

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. 

 

 

ScodeicodeLcodeOcodeResultResult 2
BBBXABC0yes123
BBBCABC0yes123
BBBVABC0no0
BBBBABC0no0
BBBXABC123yes123
BBBCABC123yes123
BBBVABC0no0
BBBBABC0no0
BBBXCDE0no0
BBBCCDE0no0
BBBVCDE0yes456
BBBBCDE0yes456
BBBXCDE0no0
BBBCCDE0no0
BBBVCDE456yes456
BBBBCDE456yes456
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Result if only multi values match

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], ",")

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Result if only multi values match

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], ",")

View solution in original post

TRIPPR Frequent Visitor
Frequent Visitor

Re: Result if only multi values match

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 34 members 1,029 guests
Please welcome our newest community members: