DAX Synex EARLIER Error

[Amos_Lim]

Hi,

My code keeps showing EARLIER/EARLIEST refers to an earlier row context which doesn't exist..

Any help would appreciate. Thanks.

Rank = RANKX(FILTER('ForkLiftData', 'ForkLiftData'[SAP Number] = EARLIER('ForkLiftData'[SAP Number])), 'ForkLiftData'[ForkLift Name],,ASC)

[rohit1991]

Hi @Amos_Lim ,

The error with EARLIER in your DAX formula occurs because EARLIER needs a valid outer row context to reference, but there’s none in your calculation.

Solution

You can rewrite your formula to avoid EARLIER entirely. Use variables or ADDCOLUMN to create row contexts explicitly.

Updated Formula for a Calculated Column

If this is a calculated column:

Rank =
RANKX(
    FILTER(
        'ForkLiftData',
        'ForkLiftData'[SAP Number] = EARLIER('ForkLiftData'[SAP Number])
    ),
    'ForkLiftData'[ForkLift Name],
    ,
    ASC
)

This should work if the column is being calculated row by row. If it doesn’t, consider the alternatives below.

Alternative 1: Using VAR and ALL

To avoid EARLIER, explicitly define the context using VAR and ALL:

Rank =
VAR CurrentSAPNumber = 'ForkLiftData'[SAP Number]
RETURN
RANKX(
    FILTER(
        ALL('ForkLiftData'),
        'ForkLiftData'[SAP Number] = CurrentSAPNumber
    ),
    'ForkLiftData'[ForkLift Name],
    ,
    ASC
)

Alternative 2: Using GROUPBY (for aggregated contexts)

If you're ranking based on groups (e.g., SAP Number groups), use GROUPBY to create a new table first:

Rank =
RANKX(
    GROUPBY(
        'ForkLiftData',
        'ForkLiftData'[SAP Number],
        "ForkLiftName", MAX('ForkLiftData'[ForkLift Name])
    ),
    [ForkLiftName],
    ,
    ASC
)

When to Use These Solutions

• Use Alternative 1 if you’re creating a calculated column or need a simple row-wise ranking.
• Use Alternative 2 if you're working with aggregated groups or want more explicit control over the grouping.

[Bibiano_Geraldo]

Hi @Amos_Lim ,

Whats is the criteria for rank? what will determine to forklift stand in first position or last? please give more details to help you

[Amos_Lim]

I am trying to use DAX to rank the forklifts for each user.

and use DAX to display user details only for the first row of each user. Repeat the same DAX for other column except ForkLift Name

In summary:-

My intension was to make the table in the PBI dashboard that listed all users and the forklifts they are authorized to drive.

and currently, the table displays multiple rows of the same user, with their 'User Name', 'SAP Number', 'Forklift Name', 'POC', 'Department', and 'License Valid Until' if the user has access to different forklifts.

So for repeated user, I would like to just display their information in the 1st row, and for the 2nd or 3rd or more with the same user will just shows the different ForkLift and the rest of the columns show "Blank"?

[grazitti_sapna]

Hi @Amos_Lim, 
Please try using this DAX measure.

Rank = 
RANKX(
FILTER(
'ForkLiftData',
'ForkLiftData'[SAP Number] = SELECTEDVALUE('ForkLiftData'[SAP Number])
),
'ForkLiftData'[ForkLift Name],
,
ASC
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Amos_Lim]

It gives and error message

A single value for column 'ForkLift Name' in table 'ForkLiftData' cannot be determined. this can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result

[grazitti_sapna]

Could you please share sample data, along with your expected result and any relevant logic.

If possible, upload a simplified .pbix file using this guide:

 How to upload PBI in Community

[Amos_Lim]

I have attached a sample PBIX file along with the expected results.

Excel 

Sample File 

[Ashish_Mathur]

Hi,

Show the expected result in another column of the Excel file.

[Amos_Lim]

The Excel file contained raw data for all users, including their Name, SAP Number, POC, Department, Forklift License Valid Until date, and the Forklifts they are authorized to operate.

I duplicated the sheet to illustrate the expected outcome in the Power BI dashboard.

Excel with Expected Result Sheet 

[Amos_Lim]

I have taken a screenshot of the error and would like to resolve the issue as follows:

If a user (highlighted with a red arrow) appears for both Nichiyu and TCM forklifts, their information should only be displayed once. In the second row, only "TCM" should be shown instead of repeating the user's details. This approach will make the table easier to read, especially since it is a lengthy list.

[Ritaf1983]

Hi @Amos_Lim 

Please share sample data (excluding sensitive information) in text format, along with your expected result and any relevant logic. For guidance, refer to

How to provide sample data in the Power BI Forum

If possible, upload a simplified .pbix file using this guide:

 How to upload PBI in Community

[bhanu_gautam]

@Amos_Lim Try using this

Rank =
VAR CurrentSAPNumber = 'ForkLiftData'[SAP Number]
RETURN
RANKX(
FILTER('ForkLiftData', 'ForkLiftData'[SAP Number] = CurrentSAPNumber),
'ForkLiftData'[ForkLift Name],
,
ASC
)