cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sentsara Regular Visitor
Regular Visitor

rankx in DAX

 

Hi team,

i need top 5 from each Grp based on val desc

SQL Query

declare @CategoryRanking table
(
Grp varchar(10),
Category varchar(100),
val float
)

insert into @CategoryRanking
select 'HP','HP--0',12
UNION ALL
select 'HP','HP--1',34
UNION ALL
select 'HP','HP--2',65
UNION ALL
select 'HP','HP--3',2
UNION ALL
select 'HP','HP--4',87
UNION ALL
select 'HP','HP--4',98
UNION ALL
select 'HP','HP--5',122
UNION ALL

select 'DELL','DELL-1',455
UNION ALL
select 'DELL','DELL-2',123
UNION ALL
select 'DELL','DELL-3',456
UNION ALL
select 'DELL','DELL-4',100
UNION ALL
select 'DELL','DELL-5',500
UNION ALL
select 'DELL','DELL-6',68
UNION ALL
select 'DELL','DELL-7',300

SELECT * ,ROW_NUMBER() OVER(PARTITION BY grp ORDER BY val desc) AS RowNumb
FROM @CategoryRanking

 

Expected output:  top 5 value desc from each grp is needed in DAX:

 

 

GrpCategoryvalRowNumb
DELLDELL-55001
DELLDELL-34562
DELLDELL-14553
DELLDELL-73004
DELLDELL-21235
DELLDELL-41006
DELLDELL-6687
HPHP--51221
HPHP--4982
HPHP--4873
HPHP--2654
HPHP--1345
HPHP--0126
HPHP--327
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: rankx in DAX

Hi @sentsara 

For rank column you can use...

Rank x Colunm = 
VAR _rank = CALCULATETABLE( VALUES( YourTable[val] ), ALLEXCEPT( YourTable, YourTable[Grp] ) )
RETURN RANKX( _rank, YourTable[val],, DESC, Dense )

 

Measure to select top 5

Top 5 Measure = COUNTROWS( FILTER( YourTable, YourTable[Rank x Colunm] <=5 ) )
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski


 

View solution in original post

2 REPLIES 2
Super User
Super User

Re: rankx in DAX

Hi @sentsara 

For rank column you can use...

Rank x Colunm = 
VAR _rank = CALCULATETABLE( VALUES( YourTable[val] ), ALLEXCEPT( YourTable, YourTable[Grp] ) )
RETURN RANKX( _rank, YourTable[val],, DESC, Dense )

 

Measure to select top 5

Top 5 Measure = COUNTROWS( FILTER( YourTable, YourTable[Rank x Colunm] <=5 ) )
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski


 

View solution in original post

Highlighted
AiolosZhao Established Member
Established Member

Re: rankx in DAX

Hi @sentsara ,

 

You can create a table, make Grp, category and below measure as value, then I think you can got what you want.

measure = IF(RANKX(ALL('Table'[Category]),CALCULATE(SUMX('Table','Table'[val]))) < 6,MAX('Table'[val]))

Please try.

Aiolos Zhao

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)