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

        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