Forum Discussion

slanka's avatar
slanka
Icon for Helper I rankHelper I
7 years ago
Solved

Filter Group By in Same table

Friends,

 

I have data in one single table, Table A, in the below format,

 

EmpNameProjNameTimesheetDate
Emp1Proj11-Jul-19
Emp1Proj12-Jul-19
Emp1Proj13-Jul-19
Emp1Proj15-Jul-19
Emp1Proj18-Jul-19
Emp1Proj29-Jul-19
Emp1Proj210-Jul-19
Emp1Proj211-Jul-19
Emp1Proj212-Jul-19
Emp1Proj215-Jul-19
Emp1Proj216-Jul-19
Emp1Proj217-Jul-19
Emp1Proj218-Jul-19
Emp1Proj219-Jul-19
Emp1Proj324-Jul-19
Emp1Proj325-Jul-19
Emp1Proj326-Jul-19

 

I need to filter that data and get the MinDate and MaxDate per Employee, Per Project (everything is in the same table, tried creating Calculate(Min(Timesheedate),Filter(TableA,Groupby(empname)), it's giving circular dependency error).

 

Final expected output should be,

 

EmpNameProjNameMinDateMaxDate
Emp1Proj11-Jul-198-Jul-19
Emp1Proj29-Jul-1919-Jul-19
Emp1Proj324-Jul-1926-Jul-19

 

Need help!

Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago

    Drag EmpName and ProjName on to a table visualisation.

    Then drag TimeSheetDate on to the same table twice.

    In the Values field well, click the dropdown on the first TimeSheetDate, choose Earliest.

    Click the dropdown on the secondTimeSheetDate, choose Latest.

    You can change the date format by choosing TimeSheetDate instead of Date Hierarchy from the same dropdown.

3 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Drag EmpName and ProjName on to a table visualisation.

    Then drag TimeSheetDate on to the same table twice.

    In the Values field well, click the dropdown on the first TimeSheetDate, choose Earliest.

    Click the dropdown on the secondTimeSheetDate, choose Latest.

    You can change the date format by choosing TimeSheetDate instead of Date Hierarchy from the same dropdown.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi slanka 

    Here is my solution

    Create 3 new columns:

    Column MAX = 
CALCULATE ( MAX ( T15[TimesheetDate] ), ALLEXCEPT ( T15, T15[EmpName],T15[ProjName] ) )
    Column Min = 
CALCULATE ( MIN( T15[TimesheetDate] ), ALLEXCEPT ( T15, T15[EmpName],T15[ProjName] ) )
    NOT Mandatory, just for debugging
    MIN_MAX = 
IF(T15[Column MAX] = T15[TimesheetDate] || T15[Column Min]  = T15[TimesheetDate], 1,0)

    Create a new table:

    T16 = 
SUMMARIZE(T15,T15[EmpName], T15[ProjName], T15[Column MAX],T15[Column Min])

     

    Good Luck!
    A