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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
carrascojg
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

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

View solution in original post

5 REPLIES 5
carrascojg
Frequent Visitor

Hello Ibendlin,

Company and Country: Columns

Count and Rank: Measures

Best

Cannot reproduce.

 

lbendlin_0-1695328478504.png

 

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
BIDanny
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.  

 

BIDanny_2-1695311486711.png

 

BIDanny_1-1695311201326.png

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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