cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 2,421 guests
Please welcome our newest community members: