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

     

danextian's avatar
danextian
Icon for Super User rankSuper User
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 )
    )