Forum Discussion
Resolver ICreate a TopN DAX measure across multiple columns
Hello,
I am trying to create a series of card dashboards for the top performers in our business for each activity type. For each activity type I have a fact table with counts - screenshot example below of my 'Client meetings' fact table. Within this table each row contains a date and details of the company met with, followed by 3 columns with the names of the Consultants who attended the meeting ('Lead Consultant'; 'Consultant 1'; 'Consultant 2'). I would like to create a TopN DAX measure which returns the name of the Consultant with the most meetings - this needs to count the data in all 3 'Consultant' columns, and also be responsive to the date slicers I have on my dashboard page. In the event of a draw I should like to return 2 names. Please can anyone help?
Thank you
Quenril
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
16 Replies
- Ashish_Mathur
Super User
Hi,
In the Query Editor, select all columns other than the Consultant columns, right click and select "Unpivot Other Columns". You may delete the attribute column. Write these measures
Instances = countrows(Data)Measure = CONCATENATEX(TOPN(1,VALUES(Data[Value]),[Instances]),Data[Value],", ")Hope this helps.
QuenrilHi Ashish,
Thank you for this. I have done the unpivot which worked fine. I don't understand fully the way these measures are intended to work, however if I tell you the results they are giving me I'm hoping you understand...
The 'Instances' measure is giving me a total row count for all meetings within a given period (as I have unpivoted each Consultant is listed in a seperate row so 'Instances' is returning the total count of every Consultant going out on a visit).
The 'Measure' measure you provided me with is accepted and not returning an error, however it is returning a blank card. I tried swopping the 'Instances' measure you gave me for a measure I have calculating the total number of Consultants and that did return a name, although not the correct one (it returned the 2nd highest result, instead of the first). We are close! Any more ideas please?
Please see screenshot attached
Thank you
Quenril
- Ashish_Mathur
Hi,
Share some dummy data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
- Quenril
Hi 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
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
- Quenril
Hello Ashish,
Thank you so much for this. So the formulas are both working however Measure 2 is returning a blank result. This is actually correct as in my data table where I have unpivoted the 3 Consultant columns (which included blank fields), the majority of my 'Consultant Name' column is showing as blank. Is there a way that after I have unpivoted the columns I can remove any rows in my fact table where that 'Consultant Name' column is blank? Essentially those rows now contain duplicate data.
Thank you so much
Quenril
- Ashish_Mathur
You are welcome. In the Query Editor, after the Unpivot step, filter out the blanks/nulls.
- Quenril
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
- Ashish_Mathur
Hi,
Share the dowload link of an Excel file. Have the 2 input tables there and via Pivots/formulas show the expected result. I will try to translate those formulas into the DAX language.
- Quenril
Hi Ashish,
Please try this link: Client Meetings table for Top Consultant Recurrence.xlsx
In this example, if we assume Consultant A is no longer working for the company the result I am looking for would be Consultant D (2 Client meetings attended). In the 'Consultant lookup' table I'm happy to add a column with the departure date or a 'Current employee Y/N' column to aid the measure as needed.
Thank you
Quenril
- Ashish_Mathur
There should be a "To Date" column in the Consultant table. If you select a date range in the slicer, then for a consultant to be considered, should he/she be in the Organisation all through out the selected date range or would the consultant be considered even if he/she has been in the Organisation for a portion of the selected date range?
- Quenril
Hi Ashish,
Adding a 'To date' is no problem I can do that for my data without issue. Regardless of the data range selected, I would like only the Consultants who are in the business the date that the dashboard is being viewed to be included (i.e. the live date the line Manager is looking at the report). For example, if Consultant A was top performer and Consultant B second for both March and April 2024, but Consultant A left the business on the 20th April, if I ran the report at the end of March the result would be Consultant A, but if I ran the report at the end of April it would be Consultant B (with Consultant A being excluded from the calculation). Is that possible?
Thank you
Quenril
- Ashish_Mathur
Hi,
Share revised Excel file with a To Date column. I can try.
- Quenril
Hi Ashish,
Please try this file: Client Meetings table for Top Consultant Recurrence.xlsx
Thank you so much 😊
Quenril
