Forum Discussion

rsbin's avatar
rsbin
Community Champion
4 years ago
Solved

Help with Calculated Table

Hello, I have the following DIM_Emp table: Emp ID PositionID Status StartID EndID Flag 1 99 Active 20191122 20210106 0 1 99 Terminated 20210814 20210814   1 78 Active ...
Need Help
  • AlB's avatar
    AlB
    4 years ago

    Hi rsbin 

    This would be probably best done in PQ but if you want it in DAX:

    NewTable =
FILTER (
    Table1,
    Table1[EndID] = CALCULATE ( MAX ( Table1[EndID] ), ALLEXCEPT ( Table1, Table1[Emp ID] ) )
)

     This will keep the EndID in the table. If you want it out you can use SELECTCOLUMNS

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Please try to write the measure something like below for creating a new table.

 

 

 

 

 

New Table =
VAR _MaxEndIDtable =
GROUPBY (
'Dim_Emp',
Dim_Emp[Emp ID],
"@MaxEndID", MAXX ( CURRENTGROUP (), 'Dim_Emp'[EndID] )
)
VAR _createnewtable =
CALCULATETABLE (
'Dim_Emp',
TREATAS ( _MaxEndIDtable, 'Dim_Emp'[Emp ID], 'Dim_Emp'[EndID] )
)
RETURN
_createnewtable