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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

I think you want to check whether there is a Parent level contained in Company_id in same Person_id. 

Try this code to create a calculated column.

True/False = 
VAR _Parent =
    FILTER (
        VALUES ( 'Table'[Company_id] ),
        'Table'[[Person_id]]] = EARLIER ( 'Table'[[Person_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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymousthanks for your help.

    Your problem statement is correct: i want to check whether there is a Parent level contained in Company_id in same Person_id

     

    i loaded your pbix file and my result is different than the sreenshot you provided

     

    the top row is giving me a TRUE() where i expect a FALSE()

     

    if i extend the person_id column with a new person and run the function again, i get the following result

     the top row is now givin me the correct result but the third row is giving me a wrong result. it looks like the filter function is not filtering correctly.

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.