Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
brianlehr
Employee
Employee

Need Target Date of Parent to show up for Child Row

Hi everyone,

 

I'm sure this has been solved before but examples around the forums/web don't seem to match up for me.   I'm trying to get a new column that:

- Checks if it is blank, if so, get the Target Date of its Parent as specified by Parent ID
- If not blank, just copy the Target Date over


The second part is simple but the first part I can't seem to figure out (it's kind of like LOOKUPVALUE but I think I need an EARLIER in a calculated column.  Hopefully the board can help!

 

table.JPG

1 ACCEPTED SOLUTION

@brianlehr try following expression, add as a column

 

Desire Date = 
IF( ISBLANK( Table9[Target Date] ), 
    LOOKUPVALUE( 
        Table9[Target Date], 
        Table9[ID], 
        Table9[Parent ID] 
    ), 
    Table9[Target Date] 
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@brianlehr I believe you have relationship with parent table and with 1 to many, add following column in child table

 

Desire Column =
IF ( ISBLANK(ChildTable[Target Date] ), ReLATED( ParentTable[Target Date] ),
ChildTable[Target Date]
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Actually this is all in the same table, though there is a Hierarchial relationship, with a path and so forth.  So I dont think this answer would work.

@brianlehr yes in that case it will not work, can you share sample data and i'm sure it can be done.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Not sure how to attach files.  Can you just copy the table below to excel and use that?  IT doesnt have hierarchy technically but the ID, Parent ID relationship should be enough.

 

IDWork Item TypeParent IDTitleTarget Date
123Parent Parent Task 15/17/2019
124Child 123Child Task 1a 
125Child 123Child Task 1b4/3/2019
126Child 123Child Task 1c 
127Parent Parent Task 24/12/2019
128Child 127Child Task 2a4/12/2019
129Child 127Child Task 2b3/31/2019
130Child 127Child Task 2c 

@brianlehr try following expression, add as a column

 

Desire Date = 
IF( ISBLANK( Table9[Target Date] ), 
    LOOKUPVALUE( 
        Table9[Target Date], 
        Table9[ID], 
        Table9[Parent ID] 
    ), 
    Table9[Target Date] 
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Works perfectly!  Thanks so much.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.