Identifying parent exists in the data set

[Anonymous]

Hello,

I'm new to Power BI and tying to get familiar with the tool

I have extracted data set which has issues where the actual data has parent and child relationship. Data set has three columns id, parent id and revenue. I would like to sum the revenue of the dataset and exclude the child lines if parents exist. I have id and parent id columns, and although parent id is populated on the child lines, parent lines may not exist as I'm extracting data that have issues. I would like to flag all the child lines which have parents pulled into the data set so that I can exclude them.

I have tried to use the path function, however as parent id is populated but parent id is not found, PATH function is throwing out an error.

if the input data is like below

id             parent_id            value

a                x                         1

b                y                         2

c                z                         3

d                a                        4

e                b                        5

the expected output should be like below so that I can sum values(1+2+3) that have flags. Here the flags for c and d ids are 0 as they are covered from their parent

id             parent_id            value    flag

a                x                         1          1

b                y                         2          1

c                z                         3          1

d                a                        4           0

e                b                        5           0

Any advice?

Thanks in advance

[kentyler]

are you saying that you only want to keep lines where there is either 1) a parent id but no child id 2) a child id but not parent id 3) a child id with a parent id that does not appear in the data set

i'm assuming you have an id field, then a child id field and a parent id field

so if id =1, parent =3, child = 4 you want to discard the line if there is a row where id = 3 ?

[Anonymous]

I have added sample data to my original post. Please verify

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could use LOOKUPVALUE() function to do it.

flag =
VAR a =
    LOOKUPVALUE ( 'Table'[value], 'Table'[id], 'Table'[parent_id] )
RETURN
    IF ( ISBLANK ( a ), 1, 0 )

[Anonymous]

Hi @v-eachen-msft 

Thank you so much! Also is it possible to apply a filter on table[parent_id] if the values contain a certain string (say contains 'x') or filter based on some other column in the data?