March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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,
Solved! Go to 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. |
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. |
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. |
@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.
Hi,
Please try to write the measure something like below for creating a new table.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
99 | |
87 | |
70 | |
61 |
User | Count |
---|---|
141 | |
122 | |
105 | |
94 | |
90 |