Forum Discussion

nsbars_rt's avatar
nsbars_rt
Advocate I
6 years ago
Solved

Grouping with condition

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.
 
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

2 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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.