Creating an Index Column in a Calculate Table not in alphabetical order

[SebaSpotti]

Hi!

I have this calculate table: 

 - Dim_Seniority = DISTINCT(HC[HC Seniority Cluster])

that has those values: 

I want to create another column that index the values in tthe specific order as in picture. This column is needed for ordering some visualizations.

I tried various ways (LOOKUPVALUE, concatened IFs) but i get always an error about "circolar dipendence".

Any suggestions?

Thanks,

[Anonymous · Accepted solution]

@ryan_mayu Thanks for your contribution on this thread.

Hi @SebaSpotti ,

You can update the formula of calculated table [Dim_Seniority] as below:

Dim_Seniority =
ADDCOLUMNS (
    DISTINCT ( HC[HC Seniority Cluster] ),
    "Index",
        SWITCH (
            [HC Seniority Cluster],
            "< 1 year", 1,
            "1 year to 3 years", 2,
            "3 year to 5 years", 3,
            "5 year to 10 years", 4,
            ">= 10 years", 5,
            BLANK ()
        )
)

Best Regards

[ryan_mayu]

have you tried to use SWITCH

Column = SWITCH('Table'[HC SeniorityCluster],"< 1 year",1,"1 year to 3 years",2,3)