Forum Discussion
Create Index Column using Dax
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.
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 _calcIndexHere 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
2 Replies
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 _calcIndexHere 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 SuperUserHi 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.
