Group a minimum Datetime by Day and Technician

[mmcarthur]

I am trying to group the minimum arrivedOn datetime by both day and technician using Power BI. Here is an example of my table: 

I am tasked with finding when a technician arrived on their first job grouped by each day. It seems to me that I would need to first group the minimum arrivedOn datetime by each Technician. And then group that measure by each day. However, everytime I try this it refuses to work. 

Here is what I am trying to achieve:

Any help especially some correct Dax formulas would be very much appreciated.

Thanks!

[v-jayw-msft · Accepted solution]

Hi @mmcarthur ,

Create a new table like below:

Table 2 = 
var tmp1 = ADDCOLUMNS('Table',"date",DATEVALUE('Table'[scheduled start]))
var tmp2 = SUMMARIZE(tmp1,[date],'Table'[technician],"job number",MIN('Table'[job number]),"arrived on",MIN('Table'[arrived on]),"scheduled start",MIN('Table'[scheduled start]))
var tmp3 = ADDCOLUMNS(tmp2,"arrived - schedualed",DATEDIFF([scheduled start],[arrived on],MINUTE))
var tmp4 = ADDCOLUMNS(tmp3,"islate",IF([arrived - schedualed]>0,1,0))
return
tmp4

[mmcarthur]

@Greg_Deckler I'm sorry I forgot to @ you before!

[FreemanZ]

Supposing you have a Date column in your table named TableName, try to create a table with the code below,

 Table =

VAR Table1= 

    SUMMARIZE(

        TableName,

        TableName[Date]

        TableName[Technician],

    )

VAR Table2=

    ADDCOLUMNS(

        Table1,

        "Min Arrived On",

        CALCULATE( MIN( TableName[Arrived On] ) )

    )

VAR Table3 =

    ADDCOLUMNS(

        Table2,

        "Job Number",

        CALCULATE( MIN(TableName[Job Number])),

        "Scheduled Start",

        CALCULATE( MIN(TableName[Schduled Start]))

)

RETURN 

    Table3

 

would sugget you to provide some copiable and simplified data, so at least we can verify the code for you.

[mmcarthur]

The measure I am trying to get for this is the number first jobs for each day worked for all technicians that were late divided by the total number of first jobs for each day worked for all technicians. 

[Greg_Deckler]

@mmcarthur Can you just put Date, Technician in a Table visual and use MIN('Table'[Arrived On]) ?

[mmcarthur]

That is not gonna work for what I'm needing. I'm needing to create measures based on the grouping so I would need to know how to make sure DAX is taking the minimum arrived on time for each day for each technician.