Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Search for value in filtered rows

I have a table as below   every person is related to one or more Company_id's. There is a hierarhy of these company_id's My goal is to have a calculated column that shows me whether a company_id t...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I think there may be something wrong in _Parent part in my original code. I update my code, and you can try this to create a calculated column.

    True/False =
VAR _Parent =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[[Person_id]]] = EARLIER ( 'Table'[[Person_id]]] ) ),
        'Table'[Company_id]
    )
RETURN
    IF (
        'Table'[L1Parent]
            IN _Parent
            || 'Table'[L2Parent]
            IN _Parent
            || 'Table'[L3Parent] IN _Parent,
        TRUE (),
        FALSE ()
    )

    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.

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , A new column like

 

var _ParentPath= [ParentPath]
var _Company_id [Company_id]
var _1 = maxx(filter(Table, search(_ParentPath, [Company_id],,0) >0 ),[Company_id])
var _2 = maxx(filter(Table, search([ParentPath], _Company_id,,0) >0),[Company_id])
return
if(not(isblank(_1)) ||not(isblank(_2)) , true(), false())

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you so much for your reply but, correct me if i am wrong, if i look at the function it is searching for the [Company_id] within its own [ParentPath] and as the sample table I give, this will alsways result in a false right?

     

    The trick is to loop through the parentPath and check whether the pathitems occur in the Company_id for that specific user