DAX Index and Match?

Question

Good Afternoon,

What I am looking to do in Power Pivot is to bring a value dependant on two other columns within the same table.

What I did first: got the latest date for a name entered in the table. I now need to match an ID number based on teh name and the date.

For example:

ID   | Name    | Date        | Latest Date | ID Number
1    | John    | 1/1/2018    |  1/5/2018   |
2    | Kyle    | 1/15/2018   |  1/30/2018  |
3    | John    | 1/5/2018    |  1/5/2018   |
4    | Kyle    | 1/30/2018   |  1/30/2018  |

What I need is:

ID   | Name    | Date        | Latest Date | ID Number
1    | John    | 1/1/2018    |  1/5/2018   | 3
2    | Kyle    | 1/15/2018   |  1/30/2018  | 4
3    | John    | 1/5/2018    |  1/5/2018   | 3
4    | Kyle    | 1/30/2018   |  1/30/2018  | 4

Any ideas on how to accomplish script to accoplish this?

Answer 1

The closest equivalent is the LOOKUPVALUE function.

This should work as a calculated column:

= LOOKUPVALUE(Table1[ID], Table1[Date], [Latest Date])

---

There are other ways to do this using filtering, but the above should be the most familiar.

Answer 2

You can use LOOKUPVALUE if you will only ever have one ID number per Latest Date / Name. Using this to return the ID which matches the Name and the Latest Date:

As a calculated column;

LOOKUPVALUE ( 
    Table1[ID], 
    Table1[Name], [Name], 
    Table1[Date], [Latest Date]
)

As a measure;

Latest ID:= 
VAR LookupName = 
    IF ( 
        HASONEVALUE ( Table1[Name] ),
        VALUES ( Table1[Name] ),
        BLANK ()
    )
VAR LookupDate = 
    IF ( 
        HASONEVALUE ( Table1[Latest Date] ),
        VALUES ( Table1[Latest Date] ),
        BLANK ()
    )
RETURN
    LOOKUPVALUE ( 
        Table1[ID],
        Table1[Name], LookupName,
        Table1[Date], LookupDate
    )

However, if you have more than one ID number with a matching name and date which matches the Latest Date, then this will return an error:

A table of multiple values was supplied where a single value was expected

Instead, you could use:

Latest ID:= 
VAR LookupDate = 
    IF ( 
        HASONEVALUE ( Table1[Latest Date] ),
        VALUES ( Table1[Latest Date] ),
        BLANK ()
    )
RETURN
    CALCULATE ( 
        FIRSTNONBLANK ( Table1[ID] , 1),
        FILTER ( 
            ALLEXCEPT ( Table1, Table1[Name] ),
            Table1[Date] = LookupDate
        )
    )