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 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.
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

8 Replies

  • 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

       

       

      🙂 

       

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous, 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        sevenhills
        Icon for Super User rankSuper User
        3 years ago

        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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    If somebody has ideas there...

    Cheers