cancel
Showing results for
Did you mean:
Highlighted
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.

 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
1 ACCEPTED SOLUTION

Accepted Solutions
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], ",")```
2 REPLIES 2
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], ",")```
Frequent Visitor

Re: Result if only multi values match

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

Announcements

Challenge: Can You Solve These?

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

Community News & Announcements

Get your latest community news and announcements.

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.

Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 34 members 1,029 guests
Recent signins: