Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    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], ",")
d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

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