Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
)
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.
)
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
)
Thank you so much..
Can you please explain on th functions used in...
It would really helps..
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.
)
I don't know why I get all as 1 😪
I only replaced my table and column , right?
Any Idea?
@Anonymous I invented this once. The Mythical DAX Index - Microsoft Power BI Community. If that doesn't get you there, let me know.