Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to Filter Top N with Field Parameters?

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...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

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.