Excel PowerPivot calculated column equivalent to an array formula

[jmwbowen]

Hello,

I'm new to PowerPivot and trying to learn a few things. I have a workbook with several tables that are related to each other through primary/foreign keys.

One of the tables has an array formula field that find's a person's current location by:

looking through a list of travel movements, finding all travel movement rows where the person's name matches the name on the original table, finding the row with the newest date, and then using the "location" on that row.

What would be an equivalent formula for this in PowerPivot?

 

[macfuller]

There are several ways. Do you have a separate date table or are you relying on the date in the travel movements?

Without knowing the table structure it's hard to know the most effective, but you'd like to find the last movement date for the selected user and get the associated location. Some pseudo code would be

[Current Location] :=
CALCULATE (
    FIRSTNONBLANKVALUE ( MovementTable[Location], 1 ),
    FILTER (
        ALL ( MovementTable ),
        MovementTable[EventDate] = LASTDATE ( MovementTable[EventDate] )
            && MovementTable[Name] = SelectedName
    )
)

But of course it might vary depending on where you're taking the date from and how you're choosing the name.