Create Index Column using Dax

[NewbieDawg23]

I want to create a DAX formula in Power BI that returns the same index column shown in the image below. If the Name, Date and Account Number are the same, the first occurence will show as 1, and the subsequent occurences will show as 0.

[quantumudit · Accepted solution]

Hello @NewbieDawg23 
Thank you for providing the screenshot of your data. Here are the steps you can follow to achieve the desired results.

First, you need to add a unique column to the existing table, which can be done by adding a simple index column in the PowerQuery editor. To do so, navigate to "Transform Data" and then add the "Index Column" from the "Add Column" tab shown in the following graphic.

Please make sure to start the index From 1 as shown in the above graphic

You can now use the following DAX formula to create the desired Index column (calculated column) in your table:

Index - Calculated = 
VAR _currCombo = 'Table'[Name] & 'Table'[Date] & 'Table'[Account Number]
VAR _uid = 'Table'[Index]
VAR _prevUid =
    CALCULATE ( MAX ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[Index] < _uid ) )
VAR _prevCombo =
    CALCULATE (
        MAX ( 'Table'[Name] ) & MAX ( 'Table'[Date] ) & MAX ( 'Table'[Account Number] ),
        FILTER ( 'Table', 'Table'[Index] = _prevUid )
    )
VAR _calcIndex =
    IF ( _currCombo = _prevCombo, 0, 1 )
RETURN
    _calcIndex

Here is the screenshot of the table for comparing the calculated results with the expected results for desired Index column:

I am also attaching the Power BI file for your reference. 🙂

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a SuperUser

[FarhanJeelani]

Hi @NewbieDawg23 ,

Try Below DAX

Index = 
VAR CurrentName = [Name]
VAR CurrentDate = [Date]
VAR CurrentAccount = [Account Number]
VAR FirstOccurrence = 
    CALCULATE(
        MINX(
            FILTER(
                'YourTable', 
                [Name] = CurrentName && 
                [Date] = CurrentDate && 
                [Account Number] = CurrentAccount
            ), 
            [Date]
        )
    )
RETURN 
    IF([Date] = FirstOccurrence, 1, 0)

Please mark this as solution if it helps you. Appreciate Kudos.