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