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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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