Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rsbin
Super User
Super User

Help with Calculated Table

Hello,

I have the following DIM_Emp table:

Emp IDPositionIDStatusStartIDEndIDFlag
199Active20191122202101060
199Terminated2021081420210814 
178Active20180827201911210
2190Active20201029202101061
2150Active20200711202010220
2190Active20210128202101281
212Active20180827202007101
31Terminated20180827202101060

 

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,

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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

SU18_powerbi_badge

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.

 

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

@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]
    )

SU18_powerbi_badge

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
Super User
Super User

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

SU18_powerbi_badge

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,

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.

@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
Super User
Super User

Hi,

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

 

 

 

 

Picture1.png

 

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
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.