topic Re: Rank with two fields in Developer

Rank with two fields

carrascojg — Tue, 19 Sep 2023 20:35:08 GMT

Hi all,

I have this table:

Company Count Rank

A 23 1

B 10 2

C 5 3

D 2 4

And with this DAX code works just fine

Rank =

RANKX(

ALLSELECTED(query)

,CALCULATE

( COUNT(query[Company] ) ,ALLEXCEPT(query, query[Company] ) )

,,DESC,Dense

)

BUT, when i add a field to this output table: "Country", my output is this:

Company Count Country Rank

A 23 US 1

B 10 UK 2

C 5 AUS 1

D 2 ARG 4

How i should develop this my DAX code in order to obtain a Rank like mas first table above.?

Best

Re: Rank with two fields

lbendlin — Thu, 21 Sep 2023 01:13:21 GMT

Is "Count" a column or a measure? Do you want the rank as a column or as a measure?

Re: Rank with two fields

BIDanny — Thu, 21 Sep 2023 15:56:17 GMT

** EDIT: I realize this doesnt exactly answer your question, but this may help someone else stumbling upon this post 🙂

You could try making a string concatenation of the two Rank Columns, then sort those alphabetically.

Concatenated =

Concatenate(

right(concatenate("000000",Value('Company Sales'[Rank Sales])),6),

Concatenate("_",right(concatenate("000000",'Company Sales'[Rank Customers]),6))

)

Rank Customers = Rankx('Company Sales','Company Sales'[Customers])

Rank Sales = Rankx(all('Company Sales'),'Company Sales'[Sales])

Dan Rolfe

Dobler Data Solutions

Re: Rank with two fields

carrascojg — Thu, 21 Sep 2023 17:29:58 GMT

Hello Ibendlin,

Company and Country: Columns

Count and Rank: Measures

Best

Re: Rank with two fields

lbendlin — Thu, 21 Sep 2023 20:35:06 GMT

Cannot reproduce.

Re: Rank with two fields

carrascojg — Fri, 22 Sep 2023 12:10:14 GMT

Hello all,

Finally i've solved that way:

RANKX(

SUMMARIZE(

ALLSELECTED(A)

, A[Country.], A[Company]

)

, [Coun]

,,DESC,Dense

)

Coun =

CALCULATE(

COUNT(A[Company])

, FILTER(A, A[Country.]<>"")

)

Best