The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
HI
I do have a quiet complex report. I one of the pages I do have a ranking of top 10 parts.
Thats all working fine.
scrap_pcs_rank =
CALCULATE (
RANKX (ALL(dim_artikel), [scrap] ),
ALLSELECTED ( dim_calendar ),
KEEPFILTERS(dim_artikel[ArticleKey]),
ALLSELECTED ( dim_scrap_reason[Scrap Reason] )
)
the measure [scrap] is a simple sum, not filter or anything.
I can show my table with all Article, filter by e.g. top 10 rank, create another visuals and filter by the rank e.g. 1 and 2 and 3 to get 3 dynamic visuals based on the rank
Problem now is:
I do have some articles which I want to exclude from the rankx calculation by a filter on the page level for ArticleKey.
If I exclude the rank 1 article on the page filter, my new table start with rank 2. I want to recalculate and the old rank 2 becomes the new rank 1.
I tried to use the RANKX (ALLSELECTED(dim_artikel), [scrap] ) but then my initial top10 table is showing all numbers as the rank for each single article is 1 and therefore its showing all.
I am not sure what is wrong, why the page filter is not changing the rankx calculation
Any ideas?
thanks
Hi @Hansolu
We have not yet received a response regarding your query. Could you please confirm if your issue has been resolved? If not, kindly provide additional details so we can offer further assistance.
Thank you.
Hi @Hansolu
Thank you for reaching out to the Microsoft Fabric Community Forum.
We reviewed the scenario you raised regarding the ranking calculation not updating when certain articles are excluded or when different TopX selections are applied. We reproduced this behavior on my end using sample data and confirmed that the ranking now recalculates correctly, with the visuals updating dynamically based on the selected rank. To help you better understand the implementation and validate the approach, I’ve attached a sample PBIX file for your reference. Please review it and let me know your observations or if you notice any differences compared to your dataset.
Regards,
Microsoft Fabric Community Support Team.
HI,
thanks but thats not working as expected.
The ranking is fine now with the page filter.
Issues are now:\
Once I select TOP1 in the slicer, the two visuals should show only the selected TOPx value.
Visual 1: based on the scrap reason overall for the selected time (only artikel and scrap reason as dimension) Visual 2: based on date and artikel for last 30 days with reasons in the legend
Once I choose the e.g. top2 in the provided example the visual 1 is not filtered down to selected top2 artikel.
Visual 2 is also mixing the artikels and showing not only the scrap for the top2 artikel.
Basically, i want to get a table of top 10 per scrap for the whole selected period.
Then Visual 1 showing the distribution for the scrap reason for the selected period
Visual 2 showing a trend for total scrap for the last 30 days (like a trend)
My guess: I need to calculate the rank for the selected period and artikle only. no matter if i add the scrap reason or date, the rank of the artikel must not change. with this ranking the other visuals should be fine.
But i do not know how to make this happen.
any ideas how to fix this?
Hi
i was trying a bit today and found a solution which is working.
So i can choose from the topx selection and all my visual 1 and visual 2 will be filtered accordingly
One issue: I need to write 2 times the rankx, depending on the visual to get the correct ranks.
Final result is I will only show the green column in the final version, other measueres are only in for demonstration currently.
Maybe anyone has an idea how I can combine the rankx into only one version
Sidemark, I need to make a own measuere to show the topx selected as i want to use this with a switch to toggle between scrap pcs and cost in a later version.
thanks
Hi @Hansolu
To simplify and ensure consistency across your visuals, you can consolidate these into a single unified ranking measure. Try the below measure:Update selection measure as well:
Article Rank (Unified) =
VAR _rank =
RANKX (
ALLSELECTED ( dim_artikel ),
CALCULATE (
[scrap],
REMOVEFILTERS ( dim_calendar ),
REMOVEFILTERS ( dim_scrap_reason ) ),
,
DESC,
DENSE
)
RETURN
IF ( [scrap] > 0, _rank, BLANK() )
Update selection measure as well:
Selected TopX =
CALCULATE (
[scrap],
FILTER (
ALLSELECTED ( dim_artikel[ArticleKey] ),
[Article Rank (Unified)] = [topx_selected]
)
)
I hope this helps. If not please share the details so that we can assist you further.
Regards,
Microsoft Fabric Community Support Team.
HI
thanks, the measuer works in all 3 visuals same (All Rankings, visual 1 and visual 2)
Article Rank (Unified) =
VAR _rank =
RANKX (
ALLSELECTED ( dim_artikel ),
CALCULATE (
[scrap],
REMOVEFILTERS ( dim_calendar ),
REMOVEFILTERS ( dim_scrap_reason ) ),
,
DESC,
DENSE
)
RETURN
IF ( [scrap] > 0, _rank, BLANK() )
Just had to make a small change:
Article Rank (Unified) =
VAR _rank =
RANKX (
ALLSELECTED ( dim_artikel ),
CALCULATE (
[scrap],
ALLSELECTED( dim_calendar ), // change to allselected
REMOVEFILTERS ( dim_scrap_reason ) ),
,
DESC,
DENSE
)
RETURN
IF ( [scrap] > 0, _rank, BLANK() )
For my understanding, could you please explain what is the differnce if i use the filters (remove, allselected) inside the rankx at the measure part or wrapped in a calculate at the end?
THanks
Hi @Hansolu
The key difference lies in where the filter context is applied. By placing ALLSELECTED or REMOVEFILTERS inside the CALCULATE that feeds RANKX, you control how [scrap] is evaluated for each article during the ranking. This ensures the ranking respects the currently selected date range from your slicers while still ignoring scrap reason, so the rank remains stable across different breakdowns. In contrast, applying REMOVEFILTERS at the outer level removes the calendar filter for the entire ranking table, which causes date slicers to be ignored leading to the unexpected behavior you noticed earlier.
Regards,
Microsoft Fabric Community Support Team.
scrap_pcs_rank =
RANKX (
ALLSELECTED ( dim_artikel ),
[scrap],
,
DESC,
DENSE
)
Use this measure instead:
scrap_pcs_rank =
RANKX(
ALLSELECTED(dim_artikel[ArticleKey]),
[scrap],
,
DESC,
DENSE
)
Why?
ALLSELECTED(dim_artikel[ArticleKey]) keeps page filters (like ArticleKey filter)
Removes ALL(dim_artikel) which was ignoring page filter
Removes KEEPFILTERS(...) which was causing wrong rank = 1 for all
Result:
If you exclude an article on page filter → the rank recalculates → old rank 2 becomes new rank 1
HI
now the page filter is working but some other things are messed up
I use the rank to together with a parameter to select my ranked article. Then I show the details for this selected rank and a trend.
this measure for the selected rank and the charts are not good anymore.
For the period chart, the rankx should be calculated based on the time frame from the slicer. However, the chart should use the result of the rankx calculation but then apply the timeframe 30 days.
In other words, i want for the selected rankx a chart for the last 30 days, no matter what time was selected on the time slicer.
Do you know how i can fix this?
thanks
Get article at chosen rank (parameter):
Selected Article =
CALCULATE (
SELECTEDVALUE(dim_artikel[ArticleKey]),
FILTER (
ALLSELECTED(dim_artikel[ArticleKey]),
[scrap_pcs_rank] = SELECTEDVALUE(RankParameter[RankValue])
)
)
Trend measure (always last 30 days for that article):
Scrap Selected Rank (Last 30d) =
CALCULATE (
[scrap],
KEEPFILTERS(dim_artikel[ArticleKey] = [Selected Article]),
DATESINPERIOD(
dim_calendar[Date],
MAX(dim_calendar[Date]),
-30,
DAY
)
)
Ranking uses slicer timeframe,
Trend chart always locks to last 30 days for the selected rank’s article.
Hi, thanks but not sure how to do.
the measuere [Selected Article] is showing me the selected Rank.
the measuere [Scrap Selected Rank (Last 30d) ] is giving me an error
in addition, how can i use the Selected Articel in the visual to show only the selected articel and the breakdown of the scrap?
thanks\
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
User | Count |
---|---|
65 | |
61 | |
60 | |
53 | |
30 |
User | Count |
---|---|
181 | |
88 | |
71 | |
48 | |
46 |