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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Ghuiles
Advocate V
Advocate V

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 has only one column with the values, and one column to differenciate the scenario (so ACT, BUD etc).

I understand that RANKX works in row context, so i barely can use it on already existing measures. That is why i am trying to build these virtual tables.

I understood as well that to calculate ACT - BUD, they have first to be created in a first virtual table.

Now i am getting nuts, because whatever i do, i get the ranking "1" for all countries.

 

Here is the DAX code i use:

 

Ranking the countries =
var vTable1 = CALCULATETABLE(
    ADDCOLUMNS(
       SUMMARIZE(
            'Fact',
            'Fact'[Date],
            'Fact'[Entity],
            DimEntities[Country]),
   
       "ACT S", CALCULATE( SUM('Fact'[Value]),  'Fact'[Scenario]= "Actual")  ,
       "BUD S",  CALCULATE(SUM('Fact'[Value]), 'Fact'[Scenario]= "Budget"),
       "PY S", CALCULATE( SUM('Fact'[Value]),  'Fact'[Scenario]= "Actual", SAMEPERIODLASTYEAR(DimDate[Date]))  ,
       "BY S", CALCULATE( SUM('Fact'[Value]),  DimDate[Year]=2019)  
       ))

var vTable2 = CALCULATETABLE(
    ADDCOLUMNS(vTable1,
        "ACT vs BUD S", [ACT S]- [BUD S],
        "ACT vs PY S", [ACT S] - [PY S],
        "ACT vs BY S", [ACT S] - [BY S]
    )
)

return RANKX(vTable2, CALCULATE( sum( SummarisedTable[ACT vs BUD S])), , DESC)
 
Question 1 --> what is this summarizedtable and not the vTable2??
Question 2: how to make the ranking works?!!?
 
Thanks for any help / hint.
Cheers
G.
2 ACCEPTED SOLUTIONS
Ghuiles
Advocate V
Advocate V

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: 

View solution in original post

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

View solution in original post

8 REPLIES 8
Ghuiles
Advocate V
Advocate V

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: 

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

sevenhills_0-1684352470024.png

 

I created Profit measure as below

sevenhills_1-1684352505830.png

 

 

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

 

sevenhills_3-1684352617991.png

 

🙂 

 

Ghuiles
Advocate V
Advocate V

I am now trying my luck with the no so communicated function "RANK", without "X".

If somebody has ideas there...

Cheers

amitchandak
Super User
Super User

@Ghuiles, Try like

 

rankx(
SUMMARIZE(
allselected('Fact'),
'Fact'[Date],
'Fact'[Entity],
DimEntities[Country]), CALCULATE( SUM('Fact'[Value]), 'Fact'[Scenario]= "Actual")- CALCULATE(SUM('Fact'[Value]), 'Fact'[Scenario]= "Budget"),,desc,dense)

 

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi

 

Thanks amitchandak!
Unfortunately it does not solve the problem.

Somehow the ranking is now from 1 to 4. 

Any idea how i could debug this?

I am trying further based on your idea.

 

Cheers

G.

To debug, you can see what is the data coming upto here, like creating dax table

 

ADDCOLUMNS(
       SUMMARIZE(
            'Fact',
            'Fact'[Date],
            'Fact'[Entity],
            DimEntities[Country]),
   
       "ACT S"CALCULATESUM('Fact'[Value]),  'Fact'[Scenario]"Actual")  ,
       "BUD S",  CALCULATE(SUM('Fact'[Value]), 'Fact'[Scenario]"Budget"),
       "PY S"CALCULATESUM('Fact'[Value]),  'Fact'[Scenario]"Actual"SAMEPERIODLASTYEAR(DimDate[Date]))  ,
       "BY S"CALCULATESUM('Fact'[Value]),  DimDate[Year]=2019)  
       )
 
I don't think we need CALCULATETABLE if we are doing just a wrapper to addcolumns.

nice, but still not working 😞

At the end, i need to have it in working measure.

Going now to bed. Perhaps tomorrow will solve it. Cheers

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.