Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Find a value accross multiple columns

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! 🙂 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

2 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.