Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Top N filter on page level

  • 1.  Top N filter on page level

    Bronze Contributor
    Posted Oct 16, 2019 05:25 AM
    Edited by Rebecka Nylin Oct 17, 2019 06:43 AM

    Hi,

    I have a dynamic Top N filter on organizations, and that is presented in one visual. And that works fine.

    But now I want all other visuals to be filtered also on these Top N organizations.

    But I don't get that to work.

    This is my DAX code:

     
    SelectedTopNNumber = IF(HASONEVALUE('TopN'[Top]);Min('TopN'[Top]);10)
    AntalEval = COUNT('table1'[StatisticKey])
    OrgRankByTopN = IF(HASONEVALUE('dimOrg'[OrgName]);RANKX(ALLSELECTED('dimOrg'[OrgName]); [AntalEval]))
    ShouldOrgBeIncluded = IF([OrgRankByTopN]<=[SelectedTopNNumber];1;0)
    And this is how the visuals look (with their filters):
    Skärmklipp1.PNGSkärmklipp2.PNG
     
    So ShouldOrgBeIncluded is used as filter on the first visual, but I want it to be on page filter level (but it cannot since it's a measure).
    I can add the ShouldOrgBeIncluded measure as a filter on the other visual too (and add the
    OrgName under Legend) but that does not work properly.
    Can I fix this with some more complex DAX code or some other way?


    ------------------------------
    Rebecka Nylin
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Top N filter on page level

    Bronze Contributor
    Posted Oct 18, 2019 09:52 AM
    Hi Rebecka,

    Without creating some sample data and playing with your tables, my first thought is to create a separate table of top 5.  If your only concern is the visualization and you do not need to have the other data, then a separate table to support may be ideal.

    If you concern goes beyond visualization, then a visualization that allow for Top 5 selection may also help.  Although I am a fan of using the built in filters and keeping reports simple.  I may be able to take a deeper look sometime next week.  But try one of the above if it meets your needs.

    Finally create a dynamic parameter for your top N.  I personally do not like adding to the filters in DAX as it is expensive on performance.

    Hi Rebecka,

    Without creating some sample data and playing with your tables, my first thought is to create a separate table of top 5.  If your only concern is the visualization and you do not need to have the other data, then a separate table to support may be ideal.

    If you concern goes beyond visualization, then a visualization that allow for Top 5 selection may also help.  Although I am a fan of using the built in filters and keeping reports simple.  I may be able to take a deeper look sometime next week.  But try one of the above if it meets your needs.

    Finally create a dynamic parameter for your top N.  I personally do not like adding to the filters in DAX as it is can be expensive on performance.  Good Luck and continue to update on what worked/did not work.



    ------------------------------
    Denise Bertsch
    Senior Specialist Business Solutions Analyst
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Top N filter on page level

    Bronze Contributor
    Posted Oct 21, 2019 02:22 AM
    Hi Denise,
    I would love if you can take a look at this.
    I was also thinking about a separate table, but how can I create that table dynamically? Since N is changing and I also have other slicers which change Top N, for example a date slicer.
    I'm not sure how to use a Parameter with this instead of a slicer.

    Here's two of my slicers in the report:
     I can't share my report since it contains customer-data, but let me know if you want me to try to create a simple copy or something.

    ------------------------------
    Rebecka Nylin
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Top N filter on page level

    Bronze Contributor
    Posted Oct 22, 2019 11:42 AM
    ​Hi Rebecka,

    I may be able to later in the week or next week as I am booked this week.

    You would need to create from Power query Editor in order to make it dynamic.

    You would pull your columns into a list, or you may need some M Code.  https://blog.crossjoin.co.uk/2016/06/03/creating-tables-in-power-bipower-query-m-code-using-table/  Then I would need to play with it in order to see if the Top N Calculation at that point could be used or if the list or columns would need to be in table form first.

    You could also create a calculated table from in DAX:  https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables.  In this article, you would want to add a Top N calculation, on the table and columns, to get the table.

    If you get a chance mock up some data for me.  If I get a chance this week I can take a look but as I said I am pretty booked up.​

    Hi Rebecka,

    You can make the table dynamic either in Power Query, which is what I would try first or in DAX.

    DAX: https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables

    Power Query M Code:  https://blog.crossjoin.co.uk/2016/06/03/creating-tables-in-power-bipower-query-m-code-using-table/

    Start here and see how you do.  Based on availability I don't want to promise I can create a sample.  Potentially I will have more capacity next week.

    ​Hi Rebecka,

    I may be able to later in the week or next week as I am booked this week.

    You would need to create from Power query Editor in order to make it dynamic.

    You would pull your columns into a list, or you may need some M Code.  https://blog.crossjoin.co.uk/2016/06/03/creating-tables-in-power-bipower-query-m-code-using-table/  Then I would need to play with it in order to see if the Top N Calculation at that point could be used or if the list or columns would need to be in table form first.

    You could also create a calculated table from in DAX:  https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables.  In this article, you would want to add a Top N calculation, on the table and columns, to get the table.

    If you get a chance mock up some data for me.  If I get a chance this week I can take a look but as I said I am pretty booked up.​


    ------------------------------
    Denise Bertsch
    IS IT Senior Technology Specialist
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Top N filter on page level

    Bronze Contributor
    Posted Oct 23, 2019 01:53 AM
    Hi,
    Okey, I know how to create a dynamic table but not how to make the table consist of only Top N organizations.
    But I will try!

    ------------------------------
    Rebecka Nylin
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: Top N filter on page level

    Bronze Contributor
    Posted Oct 31, 2019 04:16 AM
    Hi Denise,
    I've tried and tried and still can't come up with an solution for this problem, so if you have time to look at this I would appreciate it big time!
    Best Regards,
    Rebecka

    ------------------------------
    Rebecka Nylin
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Top N filter on page level

    Bronze Contributor
    Posted Oct 31, 2019 07:29 AM
    Hi Rebecka,

    I may be able to take a look today and tomorrow.  Next week I will be at Ignight and will not have any time at all.  In other words it may still be a bit but I will work on it to see if I can set it up dynamically.

    ------------------------------
    Denise Bertsch
    IS IT Senior Technology Specialist
    ------------------------------

    Conference-PBI_200x200


  • 8.  RE: Top N filter on page level

    Bronze Contributor
    Posted 28 days ago
    Hi Rebecka,

    I replied in email but wanted to add into the post in case someone else is looking for the same.  Basically you will start out by creating measures recommended in this post.  

    https://www.youtube.com/watch?v=33k6LKvtJZ8

    But instead of the last measure, since it cannot be used as a filter, you can add it as a column to obtain binary results and then use the column to filter based on the Top N selection.  I hope this works and you can also reference:  

    https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563

    Allowing filtering on a page with a measure is something that can be voted on at ideas.powerbi.com.



    ------------------------------
    Denise Bertsch
    IS IT Senior Technology Specialist
    ------------------------------

    Conference-PBI_200x200