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...
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.

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.