Find a value accross multiple columns

[Anonymous]

see the attached image. how do I search for a value I know accross multiple columns and each column has multiple rows.  Tried some VLOOKUP type stuff but could not get the correct row to return.

Andy Ideas would be apprectiated! 🙂 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

I think it is not a good idea to look for [Name] in multiple Value.children columns, this will make your code complex. I suggest you to unpivot all Value.children columns to transform your table.

For reference: Unpivot columns

Rename new column headers and the new table looks like as below.

Measure:

Look for Value title by children =
CALCULATE (
    MAX ( 'Table'[Value title] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Value.children.name] = MAX ( 'Table'[Name] ) )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vojtechsima]

Hi, @Anonymous 
If I understand correctly, this should be quite simple with "MAXX" function:

FindValue = 
var __currentValue = Mapper[Name]
var __findChildren = MAXX(FILTER(Mapper, 
Mapper[Children1] = __currentValue || Mapper[Children2] = __currentValue || Mapper[Children3] = __currentValue || Mapper[Children4] = __currentValue || Mapper[Children5] = __currentValue || Mapper[Children6] = __currentValue), Mapper[Value])
return __findChildren

Edit: 
My sample data aren't perfect after close inspection, but it will showcase the issue nonetheless.