Forum Discussion
Identifying parent exists in the data set
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
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 )
4 Replies
- kentyler
Solution Sage
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 ?
- v-eachen-msft
Community Support
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 )
