Forum Discussion
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 |
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
- d_gosbell
Super User
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], ",")
Hi d_gosbell - this solution works perfectly. A huge thank you for your time!