How to Filter Top N with Field Parameters?

[jamal29]

Hello Power BI Community,

I am using a native table and a field parameter and I want the table to be sorted based on top N when selecting a field parameter. I have tried doing this on Filter pane but it doesn't work. any advice?

Please check the image and DAX below just to help you understand. I want the highlighted columns to be sorted highest to lowest/ Top N when a field parameter is selected.

PlayerStats = {

    ("Total Points per player", NAMEOF('0. Measures'[Total Points per player]), 0),

    ("Past 5 GW points", NAMEOF('0. Measures'[Past 5 GW Points per Player]), 1),

    ("Past 3 GW Points", NAMEOF('0. Measures'[Past 3 GW Points]), 2),

    ("Past GW Points", NAMEOF('0. Measures'[Past GW Points]), 3)

}

[Anonymous]

Hi @jamal29 ,

If I understand you correctly, you want to implement both the filtering of the field and the filtering of the first N of that column when selecting the parameter. You can try the following method:
Here's the rule I set up: if you choose Total Points per player, you return the entire contents of the column; if you choose Past 5 GW Points, you return the first 5 records in the column from largest to smallest; if you choose Past 3 GW Points, you return the first 3 records in the column from largest to smallest; if you choose Past GW Points, you return the first 10 records in the column from largest to smallest.
I use this DAX to create a new measure:

Measure = 
VAR _5 = RANK(DENSE, ALL('Table'), ORDERBY('Table'[Past 5 GW Points], DESC))
VAR _3 = RANK(DENSE, ALL('Table'), ORDERBY('Table'[Past 3 GW Points], DESC))
VAR _total = RANK(DENSE, ALL('Table'), ORDERBY('Table'[Total Points per player], DESC))
RETURN
IF(
    MAX('Parameter'[Parameter]) = "Past 5 GW Points",
    IF(
        _5 <= 5,
        1,
        0
    ),
    IF(
        MAX('Parameter'[Parameter]) = "Past 3 GW Points",
        IF(
            _3 <= 3,
            1,
            0
        ),
        IF(
            MAX('Parameter'[Parameter]) = "Past GW Points",
            IF(
                _total <= 10,
                1,
                0
            ),
            1
        )
    )
)

Follow the screenshot below to set it up:

The final output is as below:

If you need to realize the data in the table is also arranged from large to small, this can not be achieved using DAX, only you can manually click here to achieve:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jamal29]

Hi Dino,
Really appreciate for your comment here. I can see your measure has variables related to a specific table such as,

VAR _5 = RANK(DENSE, ALL('Table'), ORDERBY('Table'[Past 5 GW Points], DESC))

VAR _3 = RANK(DENSE, ALL('Table'), ORDERBY('Table'[Past 3 GW Points], DESC))

VAR _total = RANK(DENSE, ALL('Table'), ORDERBY('Table'[Total Points per player], DESC))

This is clearly reading from a table. Now I can try and do the way you have done it but i will have to create calculated columns and then follow your approach.
My question is, can I use measures as variables? So the Past 5 GW points, Past 3 GW points, Total Points per player are currently all measures on my project. Looking ahead, I may add some more so understanding the process will be of great benefit.

Thanks.

[Anonymous · Accepted solution]

Hi @jamal29 ,

Sorry for being late! If the Past 5 GW points, Past 3 GW points, Total Points per player are currently all measures, it can still be achieved after my test:

And the measure is like this:

Measure 2 = 
VAR _5 = RANK(DENSE, ALL('Table'), ORDERBY('Table'[_Past 5], DESC))
VAR _3 = RANK(DENSE, ALL('Table'), ORDERBY('Table'[_Past 3], DESC))
VAR _total = RANK(DENSE, ALL('Table'), ORDERBY('Table'[_Total], DESC))
RETURN
IF(
    MAX('Parameter 2'[Parameter]) = "_Past 5",
    IF(
        _5 <= 5,
        1,
        0
    ),
    IF(
        MAX('Parameter 2'[Parameter]) = "_Past 3",
        IF(
            _3 <= 3,
            1,
            0
        ),
        IF(
            MAX('Parameter 2'[Parameter]) = "_Total",
            IF(
                _total <= 10,
                1,
                0
            ),
            1
        )
    )
)

The final output is as below:

Note: It is on the Page2 in the pbix.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lucadelicio]

can you post the pbix please?