Create a summarised table to identify the earliest time of a date, filtered by multiple criteria

[Anonymous]

Hi all,

I need some help. 

I have a table that contains the following fields:

UserID
SiteID

Start Time (UK Date and Time eg. 31/12/2019 09:00)

Finish Time (UK Date and Time eg. 31/12/2019 17:00)

I want to group by the following:

same userID

SiteID

Start Time (Date only)

and identify the earliest date and the latest dates. 

Can you help?

[Anonymous · Accepted solution]

You can use GroupBy in power query:

or you can use dax:

Table = 
ADDCOLUMNS(
    GROUPBY('Start', 'Start'[UserID], 'Start'[SiteID]),
    "Min Start", CALCULATE( FIRSTDATE( 'Start'[Start ])),
    "Max End", CALCULATE( LASTDATE( 'Start'[Finish])))

[Anonymous]

Thanks for reply Nick,

I'm brand new to PowerBI, so how do I get to Power Query? 

Also, which of DAX or Power Query is the better choice?

[Anonymous]

Sorry @Anonymous I'm a buffoon and missed the Edit Query bit!

[Anonymous]

@Anonymous 

Lol, no worries 🙂 

to use the dax, go to modeling--> New Table

Since these are tables, they only get updated when you refresh the data. Unlike measures, which are updated when they are used ( like in a table). So the choice is yours between dax and power query ( though, that is a really simplified way of thinking about it, but dont want to get into the deeper topics too fast)