Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rankx not working properly with dimensions

Dear all,

 

I experience some rather strange results using the Rankx measure. I would have much more prefered to simply share the model, but that is not possible due to containing corporate information.

Next, I have created a small mockup model where I have found the problem I am seeing in my real model to be absent. I have no clue why. 

I would like to share some insights into my model and I would be godamn awesome if one you might give me a trace of what is going wrong.

I would simply try to make a ranking of sales by store which can be sliced by a category slicer. The category slicer is filled by a seperate dimension in the model, so it is seperated from the fact table.

 

I am using the following DAX for my ranking metric. I am aware the "ALLSELECTED" wil enable recalculating the ranking based on my store selection. This is exactly as it should be.

 

Rank Sales =
RANKX (
          ALLSELECTED( DimStore[Store] );
          CALCULATE (
                 [Sales Value Net - EUR]
           )
)
 
Where [Sales Value Net - EUR] = sum(Sales[Sales])
 
 
Putting this rank sales into a table with [Sales Value Net - EUR]  will give a correct rating.
When no slicer selections active, you will see the Sales column to be correctly filtered.
However, the ranking measure will randomly give strange results when an other dimension is used linked to sales.
 

Link to picture 

 

As you can see that place is missing and we see multiple ranking double while sales value is not equal. This problem disappears once we remove the dimension slicering. 

 

Does anyone have an idea what could go wrong?

 Thanks in advance.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

it's really hard to do some evaluation without model schema and evidence of involved dimensions.

Are there many-to-many relationships?

Has DimStore[Store] unique values in DimStore table?

 

Lorenzo

 

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

Generally, this issue should be caused by ALLSELECTED(), please use ALLSELECTED(Table) instead of ALLSELECTED(Table[Column]) and check if this issue persists. If this issue still persist, please share some sample data.

Rank Sales =
RANKX (
    ALLSELECTED ( DimStore );
    CALCULATE ( SUM ( Sales[Sales] ) );
    ;
    desc;
    DENSE
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi,

it's really hard to do some evaluation without model schema and evidence of involved dimensions.

Are there many-to-many relationships?

Has DimStore[Store] unique values in DimStore table?

 

Lorenzo

 

Anonymous
Not applicable

Thank you for your answer. You are completely right that there is no way of assessing without the model schema.

 

What I did not check was the relationship between my filtering dimension and the Fact table. Somehow and I do not understand why, a single way many-to-many relationship was active which randomly gave results (it was different per device looking at the ranking).

 

Ironically, your comment about your many-to-many relationships made me tear down the model to the basics discovering this faulty relationship. When changed to the many-to-one I am seeing correct results. Thanks a lot.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.