Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Rankx on virtual tables return only 1

Hello guys   I am trying within one measure to generate the ranking by country based on ACT vs BUD. The fact table and the dimension table DimEntities are connected in the model. The fact table h...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi guys

     

    I think i found the solution:

    VAR _tmpTable = SUMMARIZE(ALLSELECTED('Fact'), DimEntities[Country],  "Value1", [ACT Vs BUD])
    VAR _curCountry = SELECTEDVALUE(DimEntities[Country])
    VAR _rankedTable = ADDCOLUMNS(FILTER(_tmpTable, [Country] <> BLANK()), "rank",
        RANKX(_tmpTable,  [Value1], ,DESC,Skip))
    RETURN
    MAXX(FILTER(_rankedTable,  [Country] = _curCountry ),  [rank])

    My inspiration came from this post: 
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi guys

    Following this idea 

    I have been able to solve my current problem.
    This measure is now working:
    Ranking based on RANK 2h WORKING =
    VAR _tmpTable = SUMMARIZE(ALLSELECTED('Fact'), DimEntities[Country],  "Value1", [ACT Vs BUD])
    VAR _curCountry = SELECTEDVALUE(DimEntities[Country])
    VAR _rankedTable = ADDCOLUMNS(FILTER(_tmpTable, [Country] <> BLANK()), "rank",
        RANKX(_tmpTable,  [Value1], ,DESC,Skip))
    RETURN
    MAXX(FILTER(_rankedTable,  [Country] = _curCountry ),  [rank])
     
    Cheers & thanks for your help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi guys

 

I think i found the solution:

VAR _tmpTable = SUMMARIZE(ALLSELECTED('Fact'), DimEntities[Country],  "Value1", [ACT Vs BUD])
VAR _curCountry = SELECTEDVALUE(DimEntities[Country])
VAR _rankedTable = ADDCOLUMNS(FILTER(_tmpTable, [Country] <> BLANK()), "rank",
    RANKX(_tmpTable,  [Value1], ,DESC,Skip))
RETURN
MAXX(FILTER(_rankedTable,  [Country] = _curCountry ),  [rank])

My inspiration came from this post: 
sevenhills's avatar
sevenhills
Icon for Super User rankSuper User
3 years ago

Glad you could resolve it.

 

RANKX is simple function but not easy to use.

 

 

I am adding for others future reference and for you also:

I believe you are looking to calculate rank dynamically based on multiple (table) columns.

 

You can check this article for more understanding:

https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

Section: Dynamic ranking on Rounded Sales and customer name

 

https://www.sqlbi.com/articles/introducing-rankx-in-dax/ 

https://blog.enterprisedna.co/rankx-dax-function-in-power-bi-a-comprehensive-overview/ 

 

I am still puzzled , whether this approach works or not in your scenario:

  • I am using AdventureWorks Sales as example.
  • Sales table has transactions, dimensions are Product, Sales Territory and linked to Sales table

 

I created Profit measure as below

 

 

I created rank measure as below

Rank Profit By Product Category By Sales Territory Country = 
 
 IF ( HASONEVALUE('Product'[Category]) &&  HASONEVALUE('Sales Territory'[Country]) && not ISBLANK(Sales[Profit]), 
    RANKX ( ALLSELECTED(Sales), CALCULATE( [Profit], ALLEXCEPT(Sales, 'Product'[Category], 'Sales Territory'[Country])) , , DESC, DENSE) 
 )

I see the output as below

 

 

🙂