topic Re: RANKING in a Matrix with hierarchies with data from 2 tables in DAX Commands and Tips

RANKING in a Matrix with hierarchies with data from 2 tables

yassine_mendy59 — Tue, 02 Sep 2025 09:38:32 GMT

Objective: Create a rank calculation in a matrix based on the number of customers.

So, with a hierarchy:

Region

Store

Department

We have table A, which contains the store ID, department, and number of customers.

We have table B, which contains the store ID, store label, and associated region label for each store ID.

The desired matrix would be:

Region (table B)

Store (table B)

Department (Table A)

SUM Number of customers (Measure)

Associated rank (Measure)

The rank should therefore be calculated for each of the hierarchies based on the number of customers.

At the service level, I have no problem with the calculation, but at the other levels (store and region), I do have some issues.

At the store level, I have a calculation, but strangely, I will have the same rank for stores with 645, 636, and 623 customers, respectively. This is illogical.

At the region level, I only have 1s.

Actual measure :

Adhesions_Rank =
VAR rank_region =
RANKX(
ALL(B[region_label]),
CALCULATE([NB_CUSTOMERS]),
,
DESC,
DENSE
)

VAR rank_magasin =
RANKX(
ALL(B[entity_label]),
CALCULATE([[NB_CUSTOMERS]]),
,
DESC,
DENSE
)

VAR rank_service =
RANKX(
ALL(A[service_label]),
CALCULATE([NB_CUSTOMERS]),
,
DESC,
DENSE
)

RETURN
SWITCH(
TRUE(),
ISINSCOPE(A[service_label]), rank_service,
ISINSCOPE(B[entity_label]), rank_magasin,
ISINSCOPE(B[region_label]), rank_region
)

Re: RANKING in a Matrix with hierarchies with data from 2 tables

Poojara_D12 — Tue, 02 Sep 2025 09:43:19 GMT

Hi yassine_mendy59

Can you try :

Adhesions_Rank = SWITCH( TRUE(), -- Department level (rank within a store) ISINSCOPE(A[service_label]), RANKX( ALLSELECTED(A[service_label]), CALCULATE([NB_CUSTOMERS]), , DESC, DENSE ), -- Store level (rank within a region) ISINSCOPE(B[entity_label]), RANKX( ALLSELECTED(B[entity_label]), CALCULATE([NB_CUSTOMERS]), , DESC, DENSE ), -- Region level (rank across all regions) ISINSCOPE(B[region_label]), RANKX( ALLSELECTED(B[region_label]), CALCULATE([NB_CUSTOMERS]), , DESC, DENSE ) )

Re: RANKING in a Matrix with hierarchies with data from 2 tables

bhanu_gautam — Tue, 02 Sep 2025 09:47:32 GMT

yassine_mendy59 , Try using

dax
Adhesions_Rank =
VAR rank_region =
RANKX(
ALL(B[region_label]),
CALCULATE(SUM(A[NB_CUSTOMERS])),
,
DESC,
DENSE
)

VAR rank_magasin =
RANKX(
ALL(B[store_label]),
CALCULATE(SUM(A[NB_CUSTOMERS])),
,
DESC,
DENSE
)

VAR rank_service =
RANKX(
ALL(A[department]),
CALCULATE(SUM(A[NB_CUSTOMERS])),
,
DESC,
DENSE
)

RETURN
SWITCH(
TRUE(),
ISINSCOPE(A[department]), rank_service,
ISINSCOPE(B[store_label]), rank_magasin,
ISINSCOPE(B[region_label]), rank_region
)

Re: RANKING in a Matrix with hierarchies with data from 2 tables

Shahid12523 — Tue, 02 Sep 2025 09:50:39 GMT

Use ALLSELECTED instead of ALL so the rank respects the parent level:

Adhesions_Rank =
SWITCH(
TRUE(),

// Rank inside Department (service) for its store
ISINSCOPE(A[service_label]),
RANKX(
ALLSELECTED(A[service_label]),
[NB_CUSTOMERS],
,
DESC,
DENSE
),

// Rank inside Store for its Region
ISINSCOPE(B[entity_label]),
RANKX(
ALLSELECTED(B[entity_label]),
[NB_CUSTOMERS],
,
DESC,
DENSE
),

// Rank inside Regions (overall)
ISINSCOPE(B[region_label]),
RANKX(
ALLSELECTED(B[region_label]),
[NB_CUSTOMERS],
,
DESC,
DENSE
)
)

👉 This way:

Departments rank within their store

Stores rank within their region

Regions rank globally

Re: RANKING in a Matrix with hierarchies with data from 2 tables

yassine_mendy59 — Tue, 02 Sep 2025 14:15:26 GMT

No changeing, always the same problem with the ranking

Re: RANKING in a Matrix with hierarchies with data from 2 tables

yassine_mendy59 — Tue, 02 Sep 2025 14:17:03 GMT

Not working either 😥

Re: RANKING in a Matrix with hierarchies with data from 2 tables

Gabry — Tue, 02 Sep 2025 14:35:40 GMT

Hey,

could you share the pbix with sample data?

Re: RANKING in a Matrix with hierarchies with data from 2 tables

yassine_mendy59 — Wed, 03 Sep 2025 15:53:28 GMT

My pbi is linked to GCP tables so i can't give you some data.

Re: RANKING in a Matrix with hierarchies with data from 2 tables

techies — Thu, 04 Sep 2025 08:55:09 GMT

Hi yassine_mendy59 please try this

Rank =

VAR rank_region =

RANKX (

ALL ( 'Table B'[region_label] ),

[NB_CUSTOMERS],

DESC,

DENSE

)

VAR rank_store =

RANKX (

ALLSELECTED ( 'Table B'[store_label] ),

[NB_CUSTOMERS],

DESC,

DENSE

)

VAR rank_service =

RANKX (

ALLSELECTED ( 'Table A'[department] ),

[NB_CUSTOMERS],

DESC,

DENSE

)

RETURN

SWITCH (

TRUE (),

ISINSCOPE ( 'Table A'[department] ), rank_service,

ISINSCOPE ( 'Table B'[store_label] ), rank_store,

ISINSCOPE ( 'Table B'[region_label] ), rank_region

)

Re: RANKING in a Matrix with hierarchies with data from 2 tables

Anonymous — Fri, 05 Sep 2025 09:44:33 GMT

Hi yassine_mendy59,

Thanks for reaching out to the Microsoft fabric community forum. It looks like your ranking expression is not evaluating a store/region-specific aggregation when RANKX iterates, so several store rows end up returning the same value to RANKX. Kindly go through the responses given by techies and bhanu_gautam and check if your issue can be resolved.

I would also take a moment to thank techies, Gabry, Shahid12523 and bhanu_gautam, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference

If I misunderstand your needs or you still have problems on it, please feel free to let us know.

Best Regards,
Hammad.

Re: RANKING in a Matrix with hierarchies with data from 2 tables

Anonymous — Mon, 08 Sep 2025 17:32:42 GMT

Hi yassine_mendy59,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well. And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

Re: RANKING in a Matrix with hierarchies with data from 2 tables

Anonymous — Sun, 14 Sep 2025 13:48:21 GMT

Hi yassine_mendy59,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.