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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Ghuiles
Advocate IV
Advocate IV

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 IV
Advocate IV

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 IV
Advocate IV

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 IV
Advocate IV

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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