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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

TopN with dynamic context filtering

Good morning,

 

I'm quite new using PBI and not able to make my filter conditions run as I want in this scenario:

 

I have 2 tables related by Category field:

- Activities table, with data about events, with its category and rating

- Probabilities table, with columns defining user profiles, plus a category column and a probability column

 

I want to filter events according to:

- user selection: user selects a category and we suggest the top 5 events by rating in that category. In SQL that would be:
select top 5 name from activities where category in ([categories selected in slicer]) order by rating

=> filter on category with a topN 3 upper by rating doesn't returns 3 elements, but the whole 5 set

 

- user profile: user defines itself and we suggest the top 5 events by rating in the 3 categories with the higher probability. In SQL that would be:
select top 5 a.name from activities a inner join probabilities p

on a.category = p.category

where p.[all columns except category and probability] = slicers.[values selected]

and p.category in (select top 3 p.category from probability p where p.[all columns except category and probability] = slicers.[values selected] order by probability desc)

order by a.rating desc

=> I'm supposed to filter the top 3 categories by probability, but I get events from all the categories

 

I've created a measure with the main categories as I need it get recalculated after slicers values (filter context), but I don't know how to filter with it:

top3cat_prob = SUMMARIZE(TOPN(3, Score_category, Score_category[Prob], DESC), Score_category[Category])


Some idea of how can I face it?

 

This is a pic to illustrate the whole thing:

 

events1.png

 

I'll apreciate all your comments and help!

KPBI

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @V-lianl-msft 

 

I've finally managed to get a top 3 categories by probability, which I filter in the "Recommended categories" slicer. I use a rank_cat < 4 where:

rank_cat = RANKX(
ALLSELECTED(Score_category[Category]),
CALCULATE(SUM(Score_category[Prob]))
,, DESC, Dense)


Despite the fact it seems to work in that slicer, that mesaure is not working in "Based on your selection" table (still don't know why). The solution I've found here is to filter by Category selecting top 3 by Probability (the same it's meant to do the rank_cat measure).

 

Now I've created a similar rank_event measure, to rank events in that top 3 categories by its rating:
rank_event =
RANKX(
ALLSELECTED(test_file[NAME]),
CALCULATE(SUM(test_file[RATING]))
,, DESC, Dense)

 

Now what I find when applying a filter by District to "Based on your selection" table is that RANKX seems not being calculating properly (or at least as I expect) the ranking. Rank_event < 3 filter returns within the 3 main categories, all lines with the selected district, not only the 2 first by Rating.

I think my problem is I'm filtering by the overall ranking, but I want the rank to act at a category level.

 

You can find the example pbix in this link: pbix file 

 

Regards

KPBI

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , Not very clear to me.

Refer if this can help in getting TOPN

https://www.youtube.com/watch?v=QIVEFp-QiOk

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hello @amitchandak, in your video the calculate function brings the sales sum as a result. In my case, I want the TOPN to bring me a list of categories (with no calculation) to use as a filter. I'm trying with a RANKX too, but I get 1 in all lines.

rank_cat = RANKX(ALLSELECTED(Score_category), CALCULATE(SUM(test_file[RATING])),, DESC)

Hi @Anonymous ,

 

You could refer to this blog:

https://blog.enterprisedna.co/find-your-top-3-salespeople-per-region-automatically-in-power-bi-using-dax/ 

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?

Please remove any sensitive data before uploading.

 

Best Regards,
Liang
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 @V-lianl-msft 

 

I've finally managed to get a top 3 categories by probability, which I filter in the "Recommended categories" slicer. I use a rank_cat < 4 where:

rank_cat = RANKX(
ALLSELECTED(Score_category[Category]),
CALCULATE(SUM(Score_category[Prob]))
,, DESC, Dense)


Despite the fact it seems to work in that slicer, that mesaure is not working in "Based on your selection" table (still don't know why). The solution I've found here is to filter by Category selecting top 3 by Probability (the same it's meant to do the rank_cat measure).

 

Now I've created a similar rank_event measure, to rank events in that top 3 categories by its rating:
rank_event =
RANKX(
ALLSELECTED(test_file[NAME]),
CALCULATE(SUM(test_file[RATING]))
,, DESC, Dense)

 

Now what I find when applying a filter by District to "Based on your selection" table is that RANKX seems not being calculating properly (or at least as I expect) the ranking. Rank_event < 3 filter returns within the 3 main categories, all lines with the selected district, not only the 2 first by Rating.

I think my problem is I'm filtering by the overall ranking, but I want the rank to act at a category level.

 

You can find the example pbix in this link: pbix file 

 

Regards

KPBI

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors