Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

get one table from another using join

Hi,

I have a somewhat weird case that I'm trying to solve. 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.

Example, Items and sales. Sales has date value and a status of inventory.
I would like to add inventory to my Items table with value being max date for each item.

 

Is this possible?

 

Thanks! 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Figured this out by using this formula:

    CALCULATE(LASTNONBLANKVALUE(Table1[Column1], FIRSTNONBLANK(Table1[Column2], 1)))

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Figured this out by using this formula:

    CALCULATE(LASTNONBLANKVALUE(Table1[Column1], FIRSTNONBLANK(Table1[Column2], 1)))
  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    Hey Anonymous , this DAX snippet will tackle the challenge you are facing:

    pull data into the one table =
MAXX(
    'many-side-table'
    ,'many-side-table'[column of interest]
)

    If this does not solve your problem, create a pbix that contains sample data, upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data share the xlsx as well.

    Also describe exactly the expected result based on the sample you are providing.

     

    Regards,
    Tom

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi TomMartens , thanks for you reply, unfortunately, not exactly what i'm looking for.

      I have reproduced my scenario in a pbix file attached in the link below. 
      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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HiAnonymous ,

     

    Has your problem been solved? if so, please consider Accept a correct reply as the solution to help others find it.
     

    Best Regards,

    Caitlyn Yan