Forum Discussion
Resolver ICreate a TopN DAX measure across multiple columns
Hello Ashish,
Amazing, that was so simple, I need to remember that filters applied in Query Editor load so thank you.
Sorry to be a pain (😶) however I have one final query to finesse this formula.... Depending on the time period I select in my slicer, sometimes the Top N Consultant Name is someone who has actually now left the business. Is there an easy way I can filter the measure to include just current Consultants? I don't want to delete the underlying data in the 'Client Meetings' table as I need that for my aggregate YOY measures etc.
I have a seperate table called 'Consultant lookup' which contains all the names, departments, codes and the dates when each person was joined the business. For people who moved teams internally that is also indicated on this sheet. Is there any way I can use this to limit my Top N formula to just individuals who are still working for our company as of the current date? I can add a further column with a simple 'In the business on current date Y/N' type selection if that helps?
This is an example of my Consultant lookup table:
Effective from date Consultant Name Division 01/01/2020 Consultant A Business Tranformation 01/04/2022 Consultant B Finance 08/04/2022 Consultant C Board 15/04/2023 Consultant D Board 07/06/2023 Consultant E Business Tranformation 31/10/2023 Consultant A Digital & Technology 31/10/2023 Consultant D Sales & Marketing 01/11/2023 Consultant F Digital & Technology Thank you
Becky
Resolver IHi Ashish,
Sure thing. Please see table image with dummy data. The result I am looking for is that I would like to return the name which occurs with the highest frequency in the 'Consultant Name' column. So in this example I would be looking for the result to be "Consultant A". In the event that there are multiple names with equal-highest recurrence, I would like the formula to return multiple names. In addition, I would like the measure to observe my date slicer from my 'Rolling calendar' table, so that the measure obeys the selected date range. Please can you help?
Thank you
Quenril
- Ashish_Mathur
Super User
I requested you to "Share data in a format that can be pasted in an MS Excel file." I cannot do anything with just an image.
- Quenril
Hi Ashish,
My apologies, I can't see how to upload an excel/csv file. Can you copy-paste from this table?
Date of Meeting Company Name Contact Name Lead of Invitee Consultant Name 31/10/2023 Company A Contact A Lead Consultant Consultant A 30/10/2023 Company B Contact B Consultant Attendee 1 Consultant B 31/10/2023 Company C Contact C Consultant Attendee 2 Consultant A 31/10/2023 Company D Contact D Lead Consultant Consultant C 31/10/2023 Company E Contact E Consultant Attendee 1 Consultant B 31/10/2023 Company F Contact F Lead Consultant Consultant D 01/11/2023 Company G Contact G Consultant Attendee 1 Consultant A 05/11/2023 Company H Contact H Lead Consultant Consultant D 16/11/2023 Company I Contact I Consultant Attendee 1 Consultant E Thank you
Quenril
- Ashish_Mathur
Hi,
These measures work
Measure = COUNTROWS(Data)Measure 2 = CONCATENATEX(TOPN(1,VALUES(Data[Consultant Name]),[Measure]),Data[Consultant Name],", ")Hope this helps.
