cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ABK
Advocate I
Advocate I

Using Distinct with number of entries to show 10 results

Hi there. 

 

I've built an app for my company to track service calls in order to register the number of errors in the past.

 

We have now decided to build further upon this and include an information page showing the top 10 frequent clients the last 3 months. 

 

But i'm having some problems:

Right now i have a Gallery set up showing the first 10 clients and a label showing the amount of times ThisItem.Result comes up. 

 

I've attached screenshots showing the layout and code - but to make it easier, I'll add them here too.

 

Gallery: FirstN(Sort(Distinct(TicketsCollect;Accountname);Result);10)

Ticket count: CountIf(TicketsCollect;ThisItem.Result = Accountname)

Client name in Gallery: Result

 

So here's what I'm actually trying to do:

I want to list the top 10 clients based on the amount of tickets they have had over the last 3 months (changing as time goes by) in descending order. 

 

I've been trying to read up on different functions but I am having a hard time incorporating this to practical use. 

 

Any tips or help is much appreciated.

 

- Anders

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

Hi @ABK ,

You might also try to get you mind around as I have commented this as best I can. I have done a test at this end on a similar data structure and got the desired results in a gallery.

With(   
   {
      vTickets:                       //set a temporary variable
      Filter(                
         TicketsCollect;              //on your data source
         DateDiff(                    //date difference
            YourDateFieldName;        //from your date field
            Today();                  //to today
            Days                      //in days
         ) < 90                       //is less than 90
      )
   };
   FirstN(                            //get the first number of records
      Sort(                           //and sort as below
         AddColumns(                  //add the count column
            GroupBy(                  //group by required field
               vTickets;              //the data set from variable above
               "Accountname";         //field mentioned above
               "OtherData"            //rest of fields go in this table record
            );
            "CountNo";                //call the count this name
            CountRows(OtherData)      //set count as total rows in OtherData
         );
         CountNo;                     //sort on count field
         Descending                   //biggest on top
      );
      10                              //and get the first 10
   )
)

This should work for you as the Items of a Gallery. Your gallery fields will be Accountname and CountNo

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

7 REPLIES 7
WarrenBelz
Super User III
Super User III

Hi @ABK ,

You might also try to get you mind around as I have commented this as best I can. I have done a test at this end on a similar data structure and got the desired results in a gallery.

With(   
   {
      vTickets:                       //set a temporary variable
      Filter(                
         TicketsCollect;              //on your data source
         DateDiff(                    //date difference
            YourDateFieldName;        //from your date field
            Today();                  //to today
            Days                      //in days
         ) < 90                       //is less than 90
      )
   };
   FirstN(                            //get the first number of records
      Sort(                           //and sort as below
         AddColumns(                  //add the count column
            GroupBy(                  //group by required field
               vTickets;              //the data set from variable above
               "Accountname";         //field mentioned above
               "OtherData"            //rest of fields go in this table record
            );
            "CountNo";                //call the count this name
            CountRows(OtherData)      //set count as total rows in OtherData
         );
         CountNo;                     //sort on count field
         Descending                   //biggest on top
      );
      10                              //and get the first 10
   )
)

This should work for you as the Items of a Gallery. Your gallery fields will be Accountname and CountNo

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

@ABK ,

Note I have updated a few things since my original post. I have tested this and it works.

Hello @WarrenBelz

 

Thank you for your help.

It seems to be working 🙂

 

If i would like to make it so entries older than 90 days arent shown if there isnt 10 new - how would I do this?

 

Right now, even if I change it to 1 day - it still shows 10 entries. 

 

The idea is to only show new entries - even if there isn't 10 new in the timeperiod. 

 

HI @ABK ,

Sorry - date reference wrong way around - typo in translating to your formula - I have fixed it in the original post.

Should be date field first and then Today() to make it a positive result.

It should work for you now.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

v-yutliu-msft
Community Support
Community Support

Hi @ABK ,

Do you mean that no matter whether time is within 90 days, you always want 10 records?

If so, you just need to not use the first part formula that he posted @WarrenBelz , and make a little change on the second part.

set the gallery's Items:

  FirstN(                            
      SortByColumns(                          
          AddColumns(                  
                  GroupBy(                  
                         TicketsCollect;              
                         "Accountname";         
                         "OtherData"
                          );
                  "CountNo";                
                   CountRows(OtherData);
                   "latestdate";
                   First(Sort(TicketsCollect;YourDateFieldName)).YourDateFieldName         
                       );  //get the latestdate
         "latestdate";   //firstly sort based on date
         "CountNo";                //then sort based on countno     
         Descending                   
      );
      10                              
   )

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@WarrenBelz thank you very much for your help.

 

I have used far too much time trying to figure out how to do this. 

@ABK no problems - I actually took that on as a bit of as challenge for myself as it combined quite a number of elements.

@v-yutliu-msft I have tested your formula on the same data set as mine and it is valid, but produces no records.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,549)