Return first name on a day - power pivot

[atkach123]

Hi,

I'm looking for a formula within PowerPivot that will allow me to create a column where the result is the first name on that day. Please refer to the link below for an example:

Imagezilla.net - Adult Image Host, Non-Adult Image Hosting

In the example, on 9/4/2015 (highlighted), this person was at two cities on that day, Whitby and Oshawa. However, they were at Whitby first, as can be seen by the "Time" column (1:00 pm vs. 2:00 pm). What I want my result column "First city on day" to return is the first city that this person was in on the day, which in this case would be Whitby.


Thanks for the help!

 

[Ozeroth]

Hi,
To calculate "First city on day" in a calculated column here's one way (change 'Data' to your actual table name):

=
CALCULATE (
    [COLOR=#ff0000][B]VALUES ( Data[City name] )[/B][/COLOR],
    CALCULATETABLE (
        FIRSTNONBLANK ( Data[Time], 0 ),
        ALLEXCEPT ( Data, Data[Day] )
    ),
    ALLEXCEPT ( Data, Data[Day] )
)

The part in red will cause an error if there are multiple cities at the earliest time on a given day.

If that matters, uou could replace it with say
IF ( HASONEVALUE ( Data[City name] ), VALUES ( Data[City name] ) )
or
FIRSTNONBLANK( Data[City name], 0 )

depending how you want this case handled.

 

[atkach123]

Thanks, Ozeroth - worked like a charm! Had to implement your suggestion of replacing "VALUES ( Data[City name] )" with "FIRSTNONBLANK( Data[City name], 0 )" because it was returning some blanks (multiple cities at earliest time).

I think my overall issue was figuring out how to get CALCULATE to return a text string but you figured it out for me, so thanks again!


Adam