topic Re: Create a Rank in DAX Commands and Tips

Create a Rank

Anonymous — Thu, 13 Aug 2020 16:27:36 GMT

Hi,

I'm trying to create a ranking of the accounts count of this table.

That table is a duplicate of another table that has the real data. I created that table using the command "grouping by" and with the operation count.

Basically, I want to know the rank of every ACCOUNT according to the number of appearances (Count) that every account has.

I created this formula, but it doesn't work, as it simply shows "1" for all accounts.

Rank =

RANKX (

ALL ( 'Tabla Account Count'[Account Name] ),

CALCULATE (

COUNTROWS ( 'Tabla Account Count' ),

ALLEXCEPT ( 'Tabla Account Count', 'Tabla Account Count'[Count] )

DESC,

DENSE

)

Thank you very much in advance!

Re: Create a Rank

amitchandak — Thu, 13 Aug 2020 16:46:15 GMT

Anonymous , why allexpect

Rank =
RANKX (
ALL ( 'Tabla Account Count'[Account Name] ),
CALCULATE (
COUNTROWS ( 'Tabla Account Count' ),
),
,
DESC,
DENSE
)

this will rank for COUNTROWS ( 'Tabla Account Count' )

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Re: Create a Rank

AntrikshSharma — Thu, 13 Aug 2020 16:50:02 GMT

Anonymous You are getting 1 because CALCULATE inititates Context Transition and adds the currently iterated Account Name to the filter context and the formula becomes,

= CALCULATE ( COUNTROWS ( 'Tabla Account Count' ), 'Tabla Account Count'[Account Name] = "Something", -- Coming from context transition ALLEXCEPT ( 'Tabla Account Count', 'Tabla Account Count'[Count] ) )

Since ALLEXCEPT being a CALCULATE modifier is evaluated after context transition the COUNTROWS that you get are for whole table and can only be filtered by the Count column.

Try this code instead:

Rank = RANKX ( ALL ( 'Tabla Account Count'[Account Name] ), CALCULATE ( COUNTROWS ( 'Tabla Account Count' ) ), , DESC, DENSE )

Re: Create a Rank

Anonymous — Thu, 13 Aug 2020 20:41:42 GMT

Hi. Thanks! But I received the same number 1 for all accounts in the ranking 😞

Re: Create a Rank

Anonymous — Thu, 13 Aug 2020 20:50:05 GMT

The following error appears :

Argument '2' in CALCULATE function is required

I don't know exactly what to put since I'm new in DAX 😕

Re: Create a Rank

AntrikshSharma — Fri, 14 Aug 2020 03:47:00 GMT

Can you share the pbix file or the diagram view of your model?

Re: Create a Rank

Anonymous — Sat, 15 Aug 2020 16:06:51 GMT

Hi Anonymous ,

You can use this measure

Rank Account Name = RANKX( ALL(Table3[Account Name]), CALCULATE(SUM(Table3[Count])),,DESC,Dense)

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!