Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
manjirit
Helper I
Helper I

Need Help with Rankx

I have a problem and can't seem to find whats going wrong!

 

I am using Rankx() function to rank the rows in the resultset table.

 

The RankX function gives two distinct ranks to the rows viz. 1 and the highest rownumber (eg. 20,000) 

 

Here is what my formula looks like:

 

Rank = RANKX (all(TableName),[All_Assets])  --- gives rank = 1 to  rows with All_assets > 0 and  rank =maxRank (something like   20,000)  to rows with All_assets = 0 

 

 I also tried: Rank = RANKX(all(TableName),calculate([All_Assets])) -- gives me the same result. And anyway I don't think I need to include Calculate since " All_assets" is a  measure which is precalculated.

 

All_Assets is a measure which is calculated as follows:

 

All_Assets = DISTINCTCOUNT(TableName[AssetName])

 

 

Any idea, what is going on?

3 REPLIES 3
Greg_Deckler
Super User
Super User

I think we need some more context around this, can you provide example/sample data? Is there some group/category column you are using that you are ranking? Here is a RANKX problem that I solved yesterday:

 

Measure = 
VAR __currentCategory = MAX(CategoryRanks[Category])
VAR __tmpTable = ALL(CategoryRanks)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,CategoryRanks[Category],"__Count",COUNT(CategoryRanks[Category]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__Rank",RANKX(__tmpTable1,[__Count],,,Skip))
RETURN MAXX(FILTER(__tmpTable2,[Category]=__currentCategory),[__Rank])

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks!

 

Here are the pictures of resultsets. I have masked some columns for confidentiality reasons. 

 

I am giving here an example of rank based on column called "Unmonitored Assets".

 

Here is how Unmonitored Assets (a measure) is calculated:

 

Unmonitored Assets = CALCULATE(DISTINCTCOUNT(TableName [AssetName]), TableName [Excepted_Assets] = 0) + 0 --> filtering on column Excepted_assets = 0 and  getting the distinct count for those assets.

 

The rank measure is as follows: 

Rank = RANKX (all(TableName),[Unmonitored Assets])  -- > I want to rank the table based on number of "Unmonitored Assets".

 

Here is what I see: 

 

Rank = 1 for all Unmonitored Assets >0 

Rank = 160001 for all Unmonitored Assets =0 

 

What is going on, anyone?

 

 

Capture2.PNGCapture3.PNGCapture.PNG

Need a sense of your source data. In that table, do [AssetName]'s appear once or multiple times? I really think that you need to do a SUMMARIZE of your table based on some grouping of which I am not clear about yet and then do an ADDCOLUMNS to add a rank column as shown in previous reply.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.