Help with Calculated Table

[rsbin]

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	20180827	20191121	0
2	190	Active	20201029	20210106	1
2	150	Active	20200711	20201022	0
2	190	Active	20210128	20210128	1
2	12	Active	20180827	20200710	1
3	1	Terminated	20180827	20210106	0

Attempting to create a CalculatedTable as follows:  For each EmpID, get the MAX of ENDID, and then give me the other 4 fields for that record.

I have tried this a number of ways and managed to confuse myself to no end.  Hoping someone can help set me straight.

Much thanks and best regards,

[AlB]

@rsbin 

Adding the SELECTCOUMNS part is simple. Here is an example for selecting two columns. If you want more, just apply the same pattern.  Check out the syntax here https://dax.guide/selectcolumns/

NewTable =
VAR aux_ =
    FILTER (
        Table1,
        Table1[EndID] = CALCULATE ( MAX ( Table1[EndID] ), ALLEXCEPT ( Table1, Table1[Emp ID] ) )
    )
RETURN
    SELECTCOLUMNS (
        aux_,
        "Emp ID", Table1[Emp ID],
        "PositionID", Table1[PositionID]
    )

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.

[AlB · Accepted solution]

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.

[rsbin]

@AlB,

Working on modifying your proposed solution.  There are other columns in the table that I need to disregard.  Can you guide me as to how I would modify with SELECTCOLUMNS so that I grab only the columns that I need?

Edit:  I think I figured out SELECTCOLUMNS:

=SELECTCOLUMNS(
FILTER(.....),

"column1", "column2," ... )

Thanks again and Kudos.

[rsbin]

@AlB, @Jihwan_Kim ,

Thank you both for the responses.  I am working in an SSAS model, so I cannot use PQ - need it in DAX.

I was attempting Jihwan's solution, but it looks like TREATAS is not a valid function.

I am now attempting AIB's solution - looks to be much simpler.

Thanks kindly to you both.  Will let you know how it works out.

[Jihwan_Kim]

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