Rankx working on visual but page filter not working

[Hansolu]

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

[v-karpurapud]

Hi @Hansolu 

Just checking in as we haven't received a response to our previous message. Were you able to review the information above? Let us know if you have any additional questions.

Thank You.

[v-karpurapud]

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.

[Hansolu]

HI

its working now, perfect, but only nearly. 

I do have a table, showing the rank, work like a charm. Issues is the performance.

THe dataset behind is bigger. Once i run it there, the rank table is still fine, the top 1 or top 2 as single article over the period is running into a query timeout

I think the better approach is to us the mentioned dax from Shaddid, but thats not working for me yet

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
)
)

I will keep trying a bit

thanks

[v-karpurapud]

Hi @Hansolu 

Thanks for the update. Hope your issue gets resolved soon. when it does, please share the insights with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

[v-karpurapud]

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.

[Hansolu]

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?

[Hansolu]

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

sample v2 

[v-karpurapud]

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.
 

[Hansolu · Accepted solution]

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

[v-karpurapud]

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.

[v-karpurapud]

Hi @Hansolu 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[Kedar_Pande]

@Hansolu 

scrap_pcs_rank =
RANKX (
ALLSELECTED ( dim_artikel ),
[scrap],
,
DESC,
DENSE
)

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[Shahid12523]

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

[Hansolu]

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

Sample PBIX 

[Shahid12523]

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.

[Hansolu]

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\

[Ashish_Mathur]

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.