Grouping with condition

[nsbars_rt]

Hello

Please advise. Simple situation but I got stuck.

I have a table that looks like that:

I need to group it by name, take min(OnDate)

and (here is the question) take the Department which was when date = min(OnDate)

If I do like:

testTable = SUMMARIZE('Table';'Table'[Name];"minDate";min('Table'[OnDate]);"minDept";min('Table'[Department]))

I get 

Grocery Department in grouping

which isn't what I want.

[V-lianl-msft · Accepted solution]

Hi @nsbars_rt ,

You can try to create a new calculated column using the if statement.

Column = 
VAR min_date = CALCULATE(MIN('TABLE'[ondate]),ALLEXCEPT('TABLE','TABLE'[name]))
return IF('TABLE'[ondate]=min_date,1)

You can filter on the table chart or create a new calculated table.

Table 2 = SELECTCOLUMNS(FILTER('TABLE','TABLE'[Column]=1),"name",'TABLE'[name],"ondate",'TABLE'[ondate],"department",'TABLE'[department])

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@nsbars_rt, Try lastnonblankvalue

https://docs.microsoft.com/en-us/dax/lastnonblankvalue-function-dax

lastnonblankvalue(Table[date],min(table[Department]))