RANKX not operating on correct level

[Anonymous]

Hi all,

I'm trying to rank blog posts by view count, but for some reason it's ranking the only each author's posts amongst themselves, as opposed to looking over the entire dataset to rank page vs page. Has anyone got a clue how to fix this?

Page Rank = 
RANKX(ALL('Hub Page Data'),[Sum of page views],1,0)

Kind regards,

Jordan 

[selimovd]

Hey @Anonymous ,

from which tables do you use the all the column in this table?

Could you provide the file if the content is not sensitive? This would make it easier to find the error.

Thank you and best regards

Denis

[Anonymous]

@selimovd Hi Denis,

Link to 1 Drive file is below. Thank you for looking into this for me!

https://1drv.ms/u/s!Aox2dQrFw5CYhmw7uhP80MiXp7Fz?e=ymowis 

Kind regards,

Jordan 

[selimovd]

Hey @Anonymous ,

in this case you have to follow 2 things:

1. Make the whole table available with ALL()

2. Use context transition for the measure itself with CALCULATE

So the following measure gives you the rank:

Rank New User =
RANKX(
    ALL( 'All Web Site Data' ),
    CALCULATE( SUM( 'All Web Site Data'[New Users] ) )
)

Let me know if that works and if you can transition that to your original problem.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[aj1973]

Hi @Anonymous 

Use you Rank measure this way 

Page Rank =
RANKX(ALL('Hub Page Data'),[Sum of page views], ,DESC)

no need for 1 and 0

[Anonymous]

still no luck 😞 

[aj1973]

Hi @Anonymous 

You need to first add a separate measure 

Sum Users = SUM('All Web Site Data'[New Users])

then add another measure

Rank = RANKX(ALL('All Web Site Data'), [Sum Users] )

By the way @selimovd we use CALCULATE only when we need to filter an expression.

[selimovd]

@aj1973 

Using a measure triggers context transition or using CALCULATE triggers context transition.

In general, we use CALCULATE when we modify the filter context, not only when we filter an expression, or we use it when we have to trigger context transition like in this case.

So both of our solutions are doing the same, your solution with an additional measure, mine with one measure.

Also, check the article from SQLBI, this will help you to understand context transition a little better:

Understanding Context Transition - SQLBI

But your approach is also totally fine.

Best regards

Denis

[aj1973]

@selimovd 

Well my apologies, may be I should have said " Better use CALCULATE to filter an expression ".

Peace 😁

[selimovd]

@aj1973 Was not meant in an offensive way, I also just recently found out about the context transition with Calculate only 😉

But it's pretty mind blowing once you dig a little deeper.

But as I said when you have 2 measures, your solution is perfect.

Best regards

Denis

[Anonymous]

Hi @pranit828 ,

I tried this but it seemed to make no difference. What else could I try?

Page Rank = 

RANKX(ALLEXCEPT('Hub Page Data','Hub Page Data'[Creator],'Hub Page Data'[Page Title]),'Hub Page Data'[Sum of page views])​

Kind regards,

Jordan 

[pranit828]

Hi @Anonymous 

Please try the below DAX

RANKX(ALLEXPECT(Table,Table[Creator],Table[Page Title]),Table[Views])