TopN with dynamic context filtering

[Anonymous]

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:

I'll apreciate all your comments and help!

KPBI

[amitchandak]

@Anonymous , Not very clear to me.

Refer if this can help in getting TOPN

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

[Anonymous]

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)

[V-lianl-msft]

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

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