Ranking fails when I add a reference table

[PhilSmith]

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

[v-nmadadi-msft]

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.

[danextian · Accepted solution]

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

[Ashish_Mathur]

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

[ribisht17]

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,