Rankx on virtual tables return only 1

[Ghuiles]

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.

[Ghuiles · Accepted solution]

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: 

 https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-Rank-based-on-more-tables/m-p/91550...

Cheers & have a good week

G.

[Ghuiles · Accepted solution]

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: 

 https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-Rank-based-on-more-tables/m-p/91550...

Cheers & have a good week

G.

[sevenhills]

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

🙂 

[Ghuiles]

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

If somebody has ideas there...

Cheers

[amitchandak]

@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

[Ghuiles]

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.

[sevenhills]

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", 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)  

       )

I don't think we need CALCULATETABLE if we are doing just a wrapper to addcolumns.

[Ghuiles]

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

[Ghuiles · Accepted solution]

Hi guys

Following this idea 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-Rank-based-on-more-tables/m-p/91550...

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