Ranking a combination of attributes based on a measure...

[Jlbaenlo]

Dear Community,

I'm looking for some help on Ranking expression...

In PowerBI, have a table
-Product, which contains the fields prdid, brand (1 Brand can contain several Prdid)
-Country, which contains the field country
a measure : m which I want to display at Brand - Country (which depends of other slicers on the visual)
I want to rank the Brand Country combination based on this measure m - which DAX formula should I use for Rankx

I cannot create a calculated table with Brand, Country and m as m depends on other slicers

so I wanted to use a virtual table VAR like

VAR Brand_Country_Combi = summarizecolumns(Product[Brand],Country[country], "mymeasure",[m])

and then use

RANKX(ALL( Brand_Country_Combi), [m],,DESC,DENSE)

but it does not work as ALL can only apply to real table not expression.

Any idea on how to overcome this ?

Many thanks

[Anonymous · Accepted solution]

Thanks for the reply from lbendlin and johnt75, please allow me to provide another insight.
Hi @Jlbaenlo ,

I am not sure how your data model is designed, here is my sample.

The data model is as follows.

The m measure is used to calculate the number of products.

Use the following measurements to calculate the ranking.

RANK = 
VAR _tb =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZECOLUMNS ( 'Product'[Brand], 'Country'[Country] ),
            "mymeasure", [m]
        ),
        ALL ()
    )
RETURN
    RANKX ( _tb, [m],, DESC, DENSE )

The final result is as follows.

Please see the pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Try

Ranking =
VAR TableToRank =
    ADDCOLUMNS (
        SUMMARIZE ( 'Product', 'Product'[Brand], Country[Country] ),
        "@m", [m]
    )
VAR Result =
    RANK ( TableToRank, ORDERBY ( [@m], DESC ) )
RETURN
    Result

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Use of RANKX in Power BI measures - SQLBI