Forum Discussion

PhilSmith's avatar
PhilSmith
Helper 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(ThisActual),BLANK(),RANKX(ALLSELECTED( CSD[Project_id] ),[Actual],,DESC,Dense))

However, when I reference project_id in Project, which is related on project_id, all of the rankings go to 1.  It is a simple one to many relationship on project_id.

In the linked model I have three tables visuals. The second and third should be identical. The first should give me rankings based on ProjectName.

Can someone explain what is going on?

Phil

RankTest.pbix

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

     

4 Replies

  • ribisht17's avatar
    ribisht17
    Super 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,

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 months ago

    Hi,

    Revise the measure to

    Licensee Rank (by Actual) = 
VAR ThisActual = [Actual]
RETURN
IF(ISBLANK(ThisActual),
    BLANK(),
    RANKX(ALLSELECTED(Project[project_id] ),[Actual],,DESC,Dense))
  • danextian's avatar
    danextian
    Super 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 )
    )

     

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    6 months ago

    Hi PhilSmith 

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.


    Thank you.