Forum Discussion
Diffucty in getting rank function when bridge table is used
Hello mufy_cw
Even though REGIONDATA is directly connected to BASE MEASURE, the ranking issue occurs because measures like RANKX evaluate over a virtual table.
If you build a virtual table using only MODIFIED BRANDS and COMPANY BY SBU, any slicers or filters on other BASE MEASURE columns won’t be included automatically. This means the rank calculation doesn’t fully honor the filter context, even with the relationship in place.
By using SUMMARIZE to create the virtual table, you ensure all relevant filters from BASE MEASURE are applied, preserve the connection with REGIONDATA, and prevent unnecessary data model expansion. That’s why the revised measure works as expected.
Regards,
Microsoft Fabric Community Support Team.
Hi mufy_cw
Thank you for reaching out to the Microsoft Fabric community forum.
The issue isn’t due to missing columns or relationships. The ranking problem occurs because your RANKX measure evaluates over a table that doesn’t fully capture the filter context from BASE MEASURE.
Since REGIONDATA is connected to BASE MEASURE (bridge) and BASE MEASURE to ABC FLAG, RANKX only considers the columns explicitly referenced. Other slicers or filters on BASE MEASURE are ignored, causing incorrect ranks.
To resolve this, create a virtual table using SUMMARIZE including MODIFIED BRANDS and COMPANY BY SBU, then rank over it:
MAT 5 Rank =
VAR RankTable =
SUMMARIZE(
'BASE MEASURES',
'BASE MEASURES'[MODIFIED BRANDS],
'BASE MEASURES'[COMPANY BY SBU],
"MAT5Value", [MAT 5]
)
RETURN
IF(
ISINSCOPE('BASE MEASURES'[MODIFIED BRANDS]) &&
ISINSCOPE('BASE MEASURES'[COMPANY BY SBU]) &&
[MAT 5] > 0,
RANKX(RankTable, [MAT5Value], , DESC, Dense)
)
This approach preserves the full filter context, avoids inflating REGIONDATA, and ensures the ranking works correctly in your matrix visuals.
If you have any more questions, please let us know and we’ll be happy to help.
Regards,
Microsoft Fabric Community Support Team.
Hi v-karpurapud
Thank you so much for the reply. I will try this and post a message here whether it worked or not. I just wanted to understand the regiondata is directly connected to base measure why it is having difficulty in filtering and calculating?
Thanks!
Hello mufy_cw
Even though REGIONDATA is directly connected to BASE MEASURE, the ranking issue occurs because measures like RANKX evaluate over a virtual table.
If you build a virtual table using only MODIFIED BRANDS and COMPANY BY SBU, any slicers or filters on other BASE MEASURE columns won’t be included automatically. This means the rank calculation doesn’t fully honor the filter context, even with the relationship in place.
By using SUMMARIZE to create the virtual table, you ensure all relevant filters from BASE MEASURE are applied, preserve the connection with REGIONDATA, and prevent unnecessary data model expansion. That’s why the revised measure works as expected.
Regards,
Microsoft Fabric Community Support Team.
Hi mufy_cw
We have not received a response from you regarding the query and were following up 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.