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 Anonymous ,

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

  • 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.