Rank with two fields

[carrascojg]

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

[carrascojg]

Hello Ibendlin,

Company and Country: Columns

Count and Rank: Measures

Best

[lbendlin]

Cannot reproduce.

[carrascojg · 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

[BIDanny]

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

[lbendlin]

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