How to get the record with the minimum date and populate on all columns

[kk_shp_user]

I have a fact table like below,

For every record in the table, I want to populate the 'first_manager' columns, which should populate 103 for the given example.

I have worked on cases where I had to sum a value across a single id but I cannot seem to solve this one since it has to involve some ranking logic. Also this is a very bgi fact table, so is there an efficient dax formula to achieve this?

[v-veshwara-msft]

Hi @kk_shp_user ,

Thanks for sharing the scenario in Microsoft Fabric Community, and also thanks to @lbendlin and @Demert for helpful inputs.

Based on what you've described - where a first_manager column needs to reflect the ManagerID associated with the earliest month_end per EmpID - there are a few valid approaches depending on your data model and requirements.

One approach is to use LOOKUPVALUE in combination with CALCULATE(MIN(...)) in a calculated column, as shown below:

first_manager = 
VAR currentEmp = 'Table'[EmpID]
VAR minDate = 
    CALCULATE(
        MIN('Table'[month_end]),
        FILTER('Table', 'Table'[EmpID] = currentEmp)
    )
RETURN
    LOOKUPVALUE(
        'Table'[ManagerID],
        'Table'[EmpID], currentEmp,
        'Table'[month_end], minDate
    )

outcome:

This retrieves the ManagerID from the row with the earliest month_end for each employee. If multiple rows share the same earliest date, one of the matching values will be returned.

This approach can perform efficiently on large fact tables as it benefits from internal column indexing, avoids intermediate row scans, and is relatively straightforward to maintain.

Other approaches shared earlier, including the one using SUMMARIZE and MINX, offer more flexibility when building logic using virtual tables. The use of window functions such as INDEX or OFFSET, as suggested by @lbendlin , can also be helpful when the data includes duplicate earliest dates and you need to control exactly which row to use.

Each method has its own strengths, and the right choice depends on how your data is structured and how the result will be used in your model.

I hope this provides clarity. Feel free to reach out if you need any further assistance.

For reference, I've attached the .pbix file demonstrating the approach.

If you found any response helpful, please consider marking it as the Accepted Solution and giving it a thumbs up to support others facing similar questions.

Thank you.

[lbendlin]

The most efficient approach is to use one of the window functions  (INDEX or OFFSET).

[Demert]

Hi @kk_shp_user ,

To achieve this I suggest you create the following calculated column:

VAR _A = 
SUMMARIZE('Table', 'Table'[EmpID],"@FirstDate",MIN('Table'[month_end   ]))

VAR _FirstDate=MINX(FILTER(_A,'Table'[EmpID]=EARLIER('Table'[EmpID])),[@FirstDate])

RETURN CALCULATE(MAX('Table'[ManagerID]), 'Table'[month_end   ], 'Table'[EmpID]=EARLIER('Table'[EmpID]), ALL('Table') , 'Table'[month_end   ] = _FirstDate)

You first create a virtual table (Summarize) per EmpID because you are only interested in the firstdate per EmpId. Next up you calculate the FirstDate Per selected (earlier) EmpID, Next up you calculate the max managerID that equals to the month end of the first date you calculated before. I added some more data and EmpID to show it works: