cancel
Showing results for
Did you mean:
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
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]))
RETURN MAXX(FILTER(__tmpTable2,[Category]=__currentCategory),[__Rank])```

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helper I

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?

Super User

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.

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements

Launching new user group features

Learn how to create your own user groups today!