locked
Pulling last date modified RRS feed

  • Question

  • I am new to DAX and I’m trying to figure something out.  I have two tables each with a product ID.  I’m trying to pull the latest FROMDATE from Table 2 with ITEMRELATION of AT17 that is associated with the same ItemID from Table 1 into the modified date column.  I’m struggling with the one to many relationship and have tried some Lookupvalues however I can’t get anything to work.  Any help would be appreciated and thanks for your time. 

    Table 2

    ITEMRELATIION From Date
    AT17 11/17/2016
    AT17 11/7/2017
    AT17 12/1/2014
    AT17 4/7/2013


    Table 1

    ItemID

    Last Modified
    AT17

    Wednesday, January 3, 2018 5:14 PM

Answers

  • HI TOwen003,

    Thanks for your question.

    Please create a calculated column called [Last Modified] in Table1 as below DAX formula:

    [Last Modified]:=
    MAXX (
        FILTER ( Table2, Table1[ItemID] = Table2[ITEMRELATION] ),
        Table2[From Date]
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by TOwen003 Thursday, January 4, 2018 3:29 PM
    Thursday, January 4, 2018 1:47 AM

All replies

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With PQ Group()
    http://www.mediafire.com/file/z3ix23ejgy1sx7w/01_03_18a.xlsx
    http://www.mediafire.com/file/478w46gz819btad/01_03_18a.pdf

    Wednesday, January 3, 2018 7:15 PM
  • HI TOwen003,

    Thanks for your question.

    Please create a calculated column called [Last Modified] in Table1 as below DAX formula:

    [Last Modified]:=
    MAXX (
        FILTER ( Table2, Table1[ItemID] = Table2[ITEMRELATION] ),
        Table2[From Date]
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by TOwen003 Thursday, January 4, 2018 3:29 PM
    Thursday, January 4, 2018 1:47 AM
  • That works perfectly.  Thanks for your time.  
    Thursday, January 4, 2018 2:55 PM