Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Using rank and Top N

Hi Everyone,

I'm trying to use TOP N feauture and RANKX function to filter a visual. TOP N is currently used to filter the years (to only show recent 2 year, ex. 2018-2019). I need a RANKX function to filter out top 20 purchase by customer, but it seems like bot of them at the same time is not working dynamically. So, does anyone have any solution for this? 

Thanks,

6 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Can you share your current measures?  It's hard to give you any sort of coherent response without knowing basics like how your data model is set up, or what you're currently trying that's failing.

     

    The best thing you could share is the .pbix file you're working with, since that will just give us all the info from the start.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I don't have any calculated measures on it. My rows are Company name, address, city, state, etc. Columns are fiscal year, fiscal qtr, and month. Values is number of sold products. I'm applying top N visual filter to the company name by sold products field to see the top 20 company at purchases, and I need another top N visual filter to fiscal year, so I can use a date field to say "Show earliest top 2 years". Since Power Bi doesn't allow using 2 top Ns on the same visual. I was thinking to do RANKX and TOPN but couldn't do it as well. By the way, everything is in one table there is no relationship.

      I'm not able to take screenshots or share the .pbix file. It's a company policy. Thank you!

      • Cmcmahan's avatar
        Cmcmahan
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        I'm assuming from your description that you're using a matrix visual?  This data model sounds like a complete mess, which will make any sort of grouping or filtering difficult.  Would it be possible to use a relative date filter for the fiscal year, and then a TOPN for the sold products?  You may want to create a date dimension for this.

         

        If you can't share screenshots or the .pbix file of the actual data per company policy, could you create a mockup with fake/anonymized sample data?  We don't need your actual numbers or whatever proprietary report layout, just data that is in the same data model layout.