Search for value in filtered rows

[Anonymous]

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 that is related to a user has ONE of its parents also related to that user. for example; user 12345 is related to Company_id 1246. this company_ID has parent 1114 which is also there. in this case it should show me a TRUE() for 12345&1246. for the combination 12345&1245, there should be a false because one if its parents are NOT part of the Company_id column . I have been plaing around but i am struggling with the result.

[Person_id]	Company_id	ParentPath	L1Parent	L2Parent	L3Parent
12345	78	1|2	1	2
12345	1114	1|2|78	1	2	78
12345	1245	1|2|88	1	2	88
12345	1246	1|2|78|1114	1	2	1114

[Anonymous]

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]

@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 · Accepted solution]

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]

@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]

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