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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Rank with two fields

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

1 ACCEPTED SOLUTION
Frequent Visitor

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
5 REPLIES 5
Frequent Visitor

Hello Ibendlin,

Company and Country: Columns

Count and Rank: Measures

Best

Super User

Cannot reproduce.

Frequent Visitor

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
Frequent Visitor

** 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
Super User

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

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors