Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to 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 ,
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |