Forum Discussion
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 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 |
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
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())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
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
