Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add calculated column to a table (one table) from many.

Hi,
Kind of a weird question. Have a one-many relationship between two tables. I need one column from my many table into the other by using a calculated column. Would like to solve this by getting the value of max date in the many column.
Have attached a sample pbix file.

What I would like is to add a calculated column to my 'one' table, with the boolean value of [action] from the 'many' table. I would like this value to be filtered by the latest date of that id (from my date table).
One example would be ItemID: 'abc'. This has both id 1 and 8 linked to it from the date table, ID 8 has the latest date, which means I would like to add: [false] as the value in my 'one' table. 
Hope that makes sense.

Thank you so much!
https://www.dropbox.com/s/7qxw78po0kb43zu/add%20column%20to%20one%20table.pbix?dl=0

  • This column expression seems to work in your one table.

    LastValue = CALCULATE(LASTNONBLANKVALUE(many[id], FIRSTNONBLANK(many[active], 1)))
     
    Pat

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee

    This column expression seems to work in your one table.

    LastValue = CALCULATE(LASTNONBLANKVALUE(many[id], FIRSTNONBLANK(many[active], 1)))
     
    Pat