How to create an Index column with random repeated column data.

[WKN]

Need some help with creating an index column in non Power Query interface. 

Data table is made up of multiple appended data tables in Power Query. Performing data sorting and creating Index column in Power Query mode induced a long loading time. How do I create a calculated column to add an index column to the data table? 

Data is sorted primarily by the Test hours column and would like to create an index column based on the ascending order of the Test hours column. However, there are multiple test data that is captured at the same Test hour for the same Serial. Example of the test data not shown, too many columns. Index column cannot have repetitive values as subsequent calculations and data filtering will reference to this index column. 

[v-yaningy-msft · Accepted solution]

Hi, @WKN 

According to your description, using DAX in desktop won't accomplish what you need if you only have two columns of data displayed without a unique identifier column. If necessary, you can add the unique identifier column and then use Rank, RANKX, ROWNUMBER functions.

You can submit an idea for here and the product team will take note of such a need.

Related Link: New tab (powerbi.com)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[WKN]

Hi, 

Continued with further online search and found below article, 

Count duplicate values and number them as 1,2,3. 

Basically created another column to count the duplicates of the repeated Test hours. This enable an identifier per row for repeated Test hours. Not perfect though but good enough for now. Have yet to try a combination of Rank, RANKX, ROWNUMBER functions. Ok will consider submitting an idea for creating an index column based on only one data column with repeated row values. 

Thanks to all who responded to this query. 

[AnalyticsWizard]

Hi @Uzi2019 

To create an index column in Power BI without using the Power Query interface, you can leverage DAX to calculate the index based on your sorting criteria. Here’s how you can achieve this:

1. Create a New Calculated Column:

   • Go to the Modeling tab in Power BI.
   • Click on New Column.
   • Name your calculated column (e.g., “Index”).

2. Write the DAX Formula:

   • Use the following DAX formula to calculate the index based on ascending Test hours:

     Index =
     RANKX(
         ALL('YourTableName'[Serial]),
         'YourTableName'[Test Hours],
         ,
         ASC
     )

   • Replace 'YourTableName' with the actual name of your data table.

3. Explanation:

   • The RANKX function assigns a unique index value to each row based on the ascending order of the Test hours column.
   • The ALL function ensures that the calculation considers all rows in the table, regardless of any filters applied.

4. Considerations:

   • Since you mentioned that there are multiple test data captured at the same Test hour for the same Serial, the index column will handle this scenario correctly by assigning distinct values.
   • The index column will be sequential and won’t have repetitive values.

Remember to adjust the formula according to your specific table and column names.

[WKN]

Hi, 
Thanks for the suggested DAX formula. I tried the formula with reference to Test hours column and because the other columns are test data results captured during tests, they are not columns with consistent incrementing values of similar properties to Test hours. Hence the following DAX formula resulted in mutliple similar Index values. 

Index = RANKX(ALL(Data), Data[Test hours], , ASC, Dense)

Is there a way to create a column to assign incrementing values to the rows with same Test hour then combine it with Test hours column i.e. making a new incrementing column, then generate a non repetitive Index column with RANKX formula?

[Uzi2019]

Hi @WKN 
Steps to follow:
Go to Transform Data 

Go to power query and go to Add columns > Index column

I hope I answered your question!

[WKN]

Hi, 

Thanks for your response. I would like to create the Index column with DAX because in Power Query mode, it just takes too long to load when I exit Power Query mode.