To create a DYNAMIC index column using DAX based on Rank column:

[Anonymous]

I wanted to created a DYNAMIC index column using DAX based on Rank column:

My Table: 

Table
Names	Counts
XXX	1
YYY	2
ZZZ	3
AAA	4
AAA	2
BBB	3
BBB	2
CCC	0
CCC	1
DDD	2
FFF	1

I Created a Measure column 'Total Counts' by grouping 'Names' column & created Rank based on 'Total count'

Names	Total counts	Rank
AAA	6	1
BBB	5	2
ZZZ	3	3
DDD	2	4
YYY	2	4
CCC	1	5
FFF	1	5
XXX	1	5

My exepected Output:
I need to create a Index column based on the Rank column as shown in the below table using DAX:

Names	Total counts	Rank	Index
AAA	6	1	1
BBB	5	2	2
ZZZ	3	3	3
DDD	2	4	4
YYY	2	4	5
CCC	1	5	6
FFF	1	5	7
XXX	1	5	8

[jennratten · Accepted solution]

This measure will produce the index needed based on the rank of the total counts, then breaking ties using the names sorted alphabetically.

Rank on Count and Name = 
VAR CountByName = [TotalCounts]
VAR CurrentName =
    SELECTEDVALUE ( TableOfCounts[Names] )
RETURN
    IF (
        NOT ISBLANK ( CurrentName )
            && ( CountByName > 0 ),
        VAR AllSelectedNames =
            ALLSELECTED ( TableOfCounts[Names] )
        VAR MaxNameRanked =
            CALCULATE ( COUNTROWS ( TableOfCounts ), REMOVEFILTERS () )
        VAR LookupTable =
            ADDCOLUMNS (
                AllSelectedNames,
                "@CountByName",
                    [TotalCounts] * MaxNameRanked
                        + RANKX ( AllSelectedNames, TableOfCounts[Names],, DESC, DENSE )
            )
        VAR LookupCurrentName =
            FILTER ( LookupTable, TableOfCounts[Names] = CurrentName )
        VAR CurrentValue =
            MAXX ( LookupCurrentName, [@CountByName] )
        VAR Ranking =
            RANKX ( LookupTable, [@CountByName], CurrentValue,, DENSE )
        RETURN
            Ranking
    )

[jennratten · Accepted solution]

This measure will produce the index needed based on the rank of the total counts, then breaking ties using the names sorted alphabetically.

Rank on Count and Name = 
VAR CountByName = [TotalCounts]
VAR CurrentName =
    SELECTEDVALUE ( TableOfCounts[Names] )
RETURN
    IF (
        NOT ISBLANK ( CurrentName )
            && ( CountByName > 0 ),
        VAR AllSelectedNames =
            ALLSELECTED ( TableOfCounts[Names] )
        VAR MaxNameRanked =
            CALCULATE ( COUNTROWS ( TableOfCounts ), REMOVEFILTERS () )
        VAR LookupTable =
            ADDCOLUMNS (
                AllSelectedNames,
                "@CountByName",
                    [TotalCounts] * MaxNameRanked
                        + RANKX ( AllSelectedNames, TableOfCounts[Names],, DESC, DENSE )
            )
        VAR LookupCurrentName =
            FILTER ( LookupTable, TableOfCounts[Names] = CurrentName )
        VAR CurrentValue =
            MAXX ( LookupCurrentName, [@CountByName] )
        VAR Ranking =
            RANKX ( LookupTable, [@CountByName], CurrentValue,, DENSE )
        RETURN
            Ranking
    )

[Anonymous]

Thank you so much..
Can you please explain on th functions used in...
It would really helps..

[jennratten · Accepted solution]

Sure thing!  Here is the same script, but with comments added for explanation.  Note, this page provides additional insight. https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

Rank on Count and Name = 
-------------------------------------------------------
-- Variables Applicable to the Entire Measure
-------------------------------------------------------
VAR CountByName = [TotalCounts]                  -- TotalCounts measure 
VAR CurrentName =                                -- The name for the current iteration of the table.
    SELECTEDVALUE ( TableOfCounts[Names] )
RETURN
    IF (                                      
        NOT ISBLANK ( CurrentName )              -- Make sure the name is not blank
            && ( CountByName > 0 ),              -- and the total count for the name is greater than 0.
-------------------------------------------------------
-- Variables Applicable within the Row Context
-------------------------------------------------------
        VAR AllSelectedNames =                    -- Create a table of names.
            ALLSELECTED ( TableOfCounts[Names] ) 
        VAR MaxNameRanked =                       -- Since the name indices are distinct
            CALCULATE (                           -- the max index equals the # of table rows.   
                COUNTROWS ( TableOfCounts ), 
                REMOVEFILTERS ()                  -- retrieve the max for the entire model
            )                                     -- result is a constant value that does not require a query to be computed
        VAR LookupTable =                         -- Temp table with all names ranked.
            ADDCOLUMNS (                          -- Add a column
                AllSelectedNames,                 -- to this table.
                "@CountByName",                   -- New column name
                    [TotalCounts] * MaxNameRanked -
                        + RANKX (                 -- Convert names into a number by computing the rank of the name.
                            AllSelectedNames, 
                            TableOfCounts[Names],
                            , DESC, DENSE         -- Alpha DESC: later in the alphabet = better = lower integer
                        )
            )
        VAR LookupCurrentName =                   -- Temp table for the current row context (Name).
            FILTER ( 
                LookupTable, 
                TableOfCounts[Names] = CurrentName 
            )
        VAR CurrentValue =                        -- Temp table for the current row context (CountByName). 
            MAXX ( 
                LookupCurrentName, 
                [@CountByName] 
            )
        VAR Ranking =                             -- Calculate the rank.
            RANKX ( 
                LookupTable, 
                [@CountByName], 
                CurrentValue,
                , 
                DENSE 
            )
        RETURN
            Ranking                               -- Return the rank.
    )

[JOSELUISMTZRMZ1]

I don't know why I get all as 1 😪

I only replaced my table and column , right?

Any Idea?

[Greg_Deckler]

@Anonymous I invented this once. The Mythical DAX Index - Microsoft Power BI Community. If that doesn't get you there, let me know.