How do I rank a filtered table?

[Loubot3000]

TL;DR:
I have an unpivoted table of ads data called 'Ads Shown':

'Attribute' column is the ad name. 'Value' column is either 'Selected', 'Not selected', or 'Missing data', depending on whether that UNIQUEID has seen that ad.

I also have this table of new ad names called 'New Ads':

I want to show the top 4 new ads based on some metric.

Here's my attempt so far:

The following measure returns 1 for ads that are in that new ads list, else 0.

New ads selection logic = 
VAR AdName = SELECTEDVALUE('Ads Shown'[Attribute])
VAR IsNewAd = 
    IF(
        CALCULATE(
            COUNTROWS('New Ads'),
            'New Ads'[Latest New Ads] = AdName
        ) > 0,
        1,
        0
    )
RETURN
    IsNewAd  // Return 1 for latest new ads, else return 0

I can confirm that the code is working correctly.

By putting the New Ads Selection measure as a filter into a graph that contains data from 'Ads Shown', and setting it equal to 1, you can filter it to just new ads. Like so:

Those bars indeed correspond only to the new ads. In this case, 'Ad metric' is just a measure that switches between different percentage metrics depending on the 'Hayu Metrics' filter. So in this case, it's just showing the percentage of people who selected "Love it" for the ads.

I made the following measure to rank the ads based on the selected metric:

New ads rank = 
VAR New_Ads = FILTER('Ads Shown', [New ads selection logic] = 1)
RETURN RANKX(New_Ads,[Ad metric], , DESC, Dense)

When I put this in the graph, it does seem to filter it to only new ads, but not the top 4:

Other variations of the rank measure I've tried seem to either not load, throw an error, or show all ads.

Anyone got any ideas?

[Loubot3000 · Accepted solution]

I seem to have found a simple solution in my case. What a relief.

The TOPN filter would normally take my [Ad Metric] measure to determine what values to sort by. In this case I only wanted the values for new ads, so I made a new measure that calculates that like so:

New Ads (Ad Metric) = CALCULATE([Ad metric], FILTER('Ads Shown', [New ads selection logic]))

And put it in the graph like so:

It's a miracle that the filter argument 'FILTER('Ads Shown', [New ads selection logic])' actually worked. Like, it's logical that it should work, since [New ads selection logic] is just a boolian, so it sort of creates a mask to filter the table by. Maybe my mistake before was writing '[New ads selection logic] = 1'?

But Power BI has this amazing way of not working for very simple obvious things, or making very simple obvious things extraordinarilly difficult. So I'm shocked that it worked.

[amitchandak]

@Loubot3000 , You can create a TOPN measure based on TOPN function

M1= calculate([Your measure], keepfilters(topn(10, allselected(Table[Attribute]), [Your measure], desc) )

TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448


You can also use window function
Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

[Loubot3000]

Hey thanks for this! I'm not entirely sure how to plug the code you wrote into my situation? Which measures go where? How do I ensure it's only showing ads that are in the New Ads list?