Forum Discussion

PhilSmith's avatar
PhilSmith
Icon for Helper III rankHelper III
6 months ago
Solved

Ranking fails when I add a reference table

No filters, nothing extraneous. Within a single table, CSD, I have a ranking measure that works just fine, grouped by project_id. Rank (by Actual) = VAR ThisActual = [Actual] RETURN IF(ISBLANK(Th...
Need Help
  • danextian's avatar
    danextian
    6 months ago

    Hi PhilSmith 

    That’s expected behavior. The measure is only modifying the filter context of the CSD[Project_id] column. Once additional columns are added to the visual, the filter context is split across each distinct combination of those columns. As a result, the calculation is evaluated within those more granular contexts, which changes the result. Instead apply ALLSELECTED to the Project table so whatever columns is used from this table respect the ranking. Also, don't use CSD[Project_id] in the visual.

    Licensee Rank (by Actual) = 
VAR ThisActual = [Actual]
RETURN
    IF (
        ISBLANK ( ThisActual ),
        BLANK (),
        RANKX ( ALLSELECTED ( Project ), [Actual],, DESC, DENSE )
    )

     

ribisht17's avatar
ribisht17
Icon for Super User rankSuper User
6 months ago

hi PhilSmith 

In the Project table, each row corresponds to a single project. That means when the measure evaluates, the context only contains one Project_ID. Ranking across a single value, always returns 1—because there’s nothing else to compare against in that context.

 

Try This,

 

RANKX(
ALLSELECTED(Project[Project_id]),
[Actual],
,
DESC,
Dense
)

 

I can see the correct answer now

 

Regards,