new colmun for a slicer - based on row id

[Pinherio]

Hello experts, 

I have a table below, I want to create a new column "Grand parent". I am trying to create a column, to look in "Level Index" if it is not one, then look on the previous row, if it is also not one, keep looking on the previous rows, it finds the earliest 1. When found, bring the item number. but it also needs to consider the SQU ID. meaning, keep looking for earlier level index one, for the same SKU ID.

the purpose of this column is to make a slicer. so if I filter by 444555, it should bring all the levels below for the same SKU ID.

https://docs.google.com/spreadsheets/d/1zMdjzA4sDLcBsyyNgXSIH8pk5b_Ce7wq/edit?usp=sharing&ouid=11016...

thanks in advance.

[Wilson_ · Accepted solution]

Hey Pinherio,

Looks like you need the PATH family of functions for this. First thing I did was replace your 0's with blanks. Then I added a column with this formula:

Grand Parent = 
PATHITEM ( 
    PATH ( 
        'Table'[Item Number], 
        'Table'[Parent Item Number] 
    ), 
    1 
)

I also added the path itself as a column for visualization purposes, but you only need the Grand Parent column above.

Please let me know if that doesn't work for you.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.