Rankx not working properly with dimensions

[Anonymous]

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.

[v-yuta-msft]

@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 · Accepted solution]

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]

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.