DAX - Add Index Column by Rows to a Table

[nelsonwhyu]

Hi all,

Wonder if anyone know of an easy way to add an index column to my metrics table with DAX

Metrics	Index
Metric 5Y	1
Metric 10Y	2
Metric 25Y	3
Metric 30Y	4
Metric 50Y	5

I am using the [Metrics] as a column in my Matrix, but since Matrix can't do custom sorting, and the default is by alphabetical order and it messes up the items - Metric 10Y , Metric 25Y, Metric 30Y, Metric 5Y, Metric 50Y.... I am creating the table above and link it by relationship in order to override the sorting.

The order from my data source is good so I just need a column assign 1,2,3,4,5, by rows without considering alphabetical order.

I also tried to achieve this in Power Query and to add an index column, that works, but to return a column with distinct items it processed my hundreds of files in my folder and it takes minutes for every Power Query step to run. Calculated Columns with DAX seems to be much more performant.

But any suggestion is appreciated. Thanks!

[harshnathani]

Hi @nelsonwhyu ,

You should create index column using Power Query. Please check the Power Query forum for such solutions

https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

Incase you want to create a calculated column. Assumption is that there are unique value of metrics and all the format is Metric XY where X is the number (5,10,15, 100 etc)

Rank =

RANKX('Table',CONVERT(LEFT( RIGHT('Table'[Metrics],LEN('Table'[Metrics])-SEARCH(" ",'Table'[Metrics],,LEN('Table'[Metrics]))),Len( RIGHT('Table'[Metrics],LEN('Table'[Metrics])-SEARCH(" ",'Table'[Metrics],,LEN('Table'[Metrics])))) -1),INTEGER),,ASC)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[nelsonwhyu]

Thanks @harshnathani ,

I also searched through the forum and seems like Power Query is the only way to go. Wonder if anyone have tips regarding this:

Everyday I receive hundreds of files with varying columns into my folder. The way I handle it is to transform the columns into the following format for each file using the PQ Transform File function (unpivoting the columns into {'Attribute','Value'}):

Classification	Attribute	Value
Financials	Metric 5Y	0.1
Financials	Metric 10Y	0.2
Financials	Metric 25Y	0.3
Financials	Spread	56
Energy	Metric 5Y	0.2
Energy	Metric 10Y	0.3
Energy	Metric 25Y	0.4
Energy	some other metrics	84

Then Power Query combine these file into a single query.

I try generating a separate query by Table.Distinct( 'Table' , "Attribute") , keep only the Attribute Column, and use PowerQuery to add an index column. It does what I am looking for, but the query loop through all my files in folder and take two minutes to generate the step in PQ, and any subsequent steps does the same looping so it's become time consuming to work on that query. It's okay if it takes minutes to actually load the data in, but in development stage in PQ that takes up much time if it's done that way for each step. Any tips is very much appreciated.

As for the DAX formula, thanks a lot I learned a lot from it. But as you said this is for when I have columns that are strictly "Metric XY". Unfortunately those are only subset of the columns that I could receive from files. In fact they can be "KRD 5Y, KRD 10Y,..." and together with "OAS, OAD, etc" So the formula in most cases would return a numeric value in my case.