Help! Measure to find the first value of the column with values from corresponding column.!

[Anonymous]

Hello All,

My data looks like this:

I need to find the first clock in time of each person(user_id) for each day.

I'm finding this to see if they are on time or not.

Thanks!

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saraMissBI]

Hi @Anonymous 

I need to understand your question, please. Do you want to find the first person who arrived on each day? or do you want to find the first time of each user on each day (can one user have more than one time on one day?)

[Anonymous]

thanks for your reply.

Yes I need to find the first time for each person on each day to figure out if they are on time or not.

And yes a person can have multiple ins.

Thanks,

Anand

[Nathaniel_C]

Hi @Anonymous ,

Try 

Measure =
CALCULATE (
    MAX ( Table[SCAN_DATE_TIME] ),
    FILTER ( ALLEXCEPT ( Table, USER_ID ), SCAN_TYPE = "IN" )
)

[Anonymous]

Thanks for your reply.

But your measure is not showing the first value for each day.

What I did was I made a separate date only column from the date time column and included that in the all except function.

Thanks again for your reply.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can just put the field into table visual as following to meet your requirement:

We can also create a measure and put into the formula to get the same result:

MinValueMeasure
= MIN ( 'Table'[SCAN_DATE_TIME] )

Or we can create a calculated table using DAX formula:

Table 2 =
SUMMARIZECOLUMNS (
    'Table'[USER_ID],
    'Table'[SCAN_DATE_TIME].[Date],
    "FirstTime", CALCULATE ( MIN ( 'Table'[SCAN_DATE_TIME] ), 'Table'[SCAN_TYPE] = "IN" )
)

If the name value in your table is real data, we suggest you to blank it as soon as possible.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.