RANKX based on 2 relationship tables not working

[Jaggana]

Hello Community,

I need to display the measure and the ranking of my fact table FTC_OCCP but using the DIM_CHANNEL[CHANNEL_CD] 2 tables away

My RANKX is working when I'm based on the intermediate DIM_CHANNEL_OCCP table :

Using this measure:

00Rank = 
RANKX(
  ALLSELECTED(DIM_CHANNEL_OCCP[CHANNEL_CD]),
  [00%],,DESC,Dense
)

But when I'm trying to go 1 more dimention further to DIM_CHANNEL, it's not working anymore:

The new measure:

00Rank DIM_CHAN = 
RANKX(
 ALLSELECTED(DIM_CHANNEL[CHANNEL_CD]),
 [00%],,DESC,Dense
)

I've read all posts I found for more than 4h, tried to use SUMMARIZE, SUMMARIZECOLUMNS, RELATED, nothing works.

Does someone has a leads?

[v-ssriganesh · Accepted solution]

Hi @Jaggana,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario using sample data and successfully achieved the expected output. For your reference, I am attaching the output screenshot and a .pbix file. The solution was implemented using the following measures:

00% = DIVIDE(SUM(FTC_OCCP[SALES_AMOUNT]), CALCULATE(SUM(FTC_OCCP[SALES_AMOUNT]), ALL(FTC_OCCP)))

00Rank DIM_CHAN = RANKX(ALLSELECTED(DIM_CHANNEL[CHANNEL_CD]), CALCULATE([00%], CROSSFILTER(DIM_CHANNEL[CHANNEL_CD], DIM_CHANNEL_OCCP[CHANNEL_CD], BOTH), CROSSFILTER(DIM_CHANNEL_OCCP[CHANNEL_CD], FTC_OCCP[CHANNEL_CD], BOTH)), , DESC, Dense)

The output matches the expected ranking based on the DIM_CHANNEL[CHANNEL_CD] across the two-table relationship.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Jaggana]

Thank you for your time and your help @v-ssriganesh

I downloaded your pbix file and it works fine, even without the CROSSFILTER function

00Rank DIM_CHAN = 
RANKX(
    ALLSELECTED(DIM_CHANNEL[CHANNEL_CD]),
    [00%],, DESC, Dense
)

Just to test, I also tried with adding a new Visual Calculation and it works too:

But your solution applied to my Report is falling and I don't understand why... I've simplify  the calculation to eliminate as many parameters as possible:

1. With the intermediate dimension:

00Sum_FCT_OCCP = SUM(FCT_OCCP[EARNED])

00Rank_SUM_DIM_CHAN_OCCP = 
    RANKX(
            ALLSELECTED(DIM_CHANNEL_OCCP[CHANNEL_CD]),
            [00Sum_FCT_OCCP],
            ,DESC,Dense
    )

2. With the 2nd dimension:

I tried with both measures:

00Rank_SUM_DIM_CHAN = 
    RANKX(
        ALLSELECTED(DIM_CHANNEL[CHANNEL_CD]),
        CALCULATE(
            [00Sum_FCT_OCCP]
            , CROSSFILTER(DIM_CHANNEL[CHANNEL_CD], DIM_CHANNEL_OCCP[CHANNEL_CD], Both)
            , CROSSFILTER(DIM_CHANNEL_OCCP[OCCP_CHANNEL_NM], FCT_OCCP[OCCP_CHANNEL_NM], Both)
        ),,DESC,Dense
    )

 and

00Rank_SUM_DIM_CHAN2 = 
    RANKX(
        ALLSELECTED(DIM_CHANNEL[CHANNEL_CD]),
        [00Sum_FCT_OCCP],
        ,DESC,DENSE
    )

Same result with the Visual calculation:

I checked the relationships between tables and they are OK:

I don't understand why the behaviour is different, do you have an idea?

[v-ssriganesh]

Hello @Jaggana,

Thank you for your feedback and for testing the solution. I’m glad the provided .pbix file worked for you, even without the CROSSFILTER function, and that the visual calculation approach also succeeded. However, I understand your concern about the solution not working as expected in your own report despite the correct relationships.

Here’s an updated approach:

• Sum Measure:

00Sum_FCT_OCCP = SUM(FCT_OCCP[EARNED])

• Ranking Measure for DIM_CHANNEL:

00Rank_SUM_DIM_CHAN =

RANKX(

    ALLSELECTED(DIM_CHANNEL[CHANNEL_CD]),

    CALCULATE(

        [00Sum_FCT_OCCP],

        FILTER(

            DIM_CHANNEL_OCCP,

            DIM_CHANNEL_OCCP[CHANNEL_CD] = DIM_CHANNEL[CHANNEL_CD]

        )

    ),

    , DESC, Dense

)

• The FILTER function explicitly links DIM_CHANNEL[CHANNEL_CD] to DIM_CHANNEL_OCCP[CHANNEL_CD], ensuring the CALCULATE evaluates [00Sum_FCT_OCCP] in the context of the correct intermediate table rows. This should help traverse the two-hop relationship correctly.
  
  
• Confirm that the relationships between DIM_CHANNEL[CHANNEL_CD] ↔ DIM_CHANNEL_OCCP[CHANNEL_CD] and DIM_CHANNEL_OCCP[OCCP_CHANNEL_NM] ↔ FCT_OCCP[OCCP_CHANNEL_NM] are active and set to a single direction (e.g., from DIM_CHANNEL to FCT_OCCP).

If the problem persists, please share the raw data or a sample of FCT_OCCP and the relationship details, and I can refine the solution further.

I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
Thank you.

[Jaggana]

Ok, that was really strange...

Ranking was wrong, I went to Power Query, add a randmon step on the Querie "Choose columns" for example:

Then I undo the last added step to the Querie and it still works fine (yes I refreshed my Semantic Model before), that's kind of a strange bug...

But now it's working 🙂

[bhanu_gautam]

@Jaggana Verify that the relationships between FTC_OCCP, DIM_CHANNEL_OCCP, and DIM_CHANNEL are correctly set up in your data model.

You can use SUMMARIZECOLUMNS to create a summarized table that includes the necessary columns from DIM_CHANNEL and the measure [00%].

DAX
00Rank DIM_CHAN =
VAR SummaryTable =
SUMMARIZECOLUMNS(
DIM_CHANNEL[CHANNEL_CD],
"MeasureValue", [00%]
)
RETURN
RANKX(
SummaryTable,
[MeasureValue],
[00%],
DESC,
DENSE
)