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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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