Forum Discussion
Create 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 across each activity type. I have a fact table set up for each activity type - let's use the example of my 'Client meetings' fact table. Within this table each row contains a date and details of the client meeting, followed by 4 columns with the names of the Consultants who attended the meeting. 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 4 'Consultant' columns, and also be responsive to the date slicers I have on my dashboard page. In the hypothetical example below the result I want is "Miss Peacock". In the event of a draw I should like to return 2 names.
For another TopN measure I would also like to be able to include additional criteria such as Consultant who has attended most meetings which are 'Cross Practice', so adding in a filter for "Y" on the 'Cross Practice' column.
| Date | Client name | Lead Consultant | Consultant Attendee 1 | Consultant Attendee 2 | Consultant Attendee 3 | Consultant Attendee 4 | Cross Practice? |
| 21/04/24 | Client A | Miss Scarlett | Miss Peacock | Colonel Mustard | Y | ||
| 21/04/24 | Client B | Miss Peacock | Colonel Mustard | Professor Plum | Y | ||
| 22/04/24 | Client C | Reverend Green | N | ||||
| 23/04/24 | Client A | Reverend Green | Miss Peacock | Y |
I hope someone can help.
Thank you
Quenril
Hello parry2k,
It isn't no, as my real table has names and sensitive data. I simply reproduced what my fact table looks like in terms of column headings and data types. Is this enough info for you to understand my dilemma?
thank you
Quenril
3 Replies
- Quenril
Resolver I
Hello parry2k,
It isn't no, as my real table has names and sensitive data. I simply reproduced what my fact table looks like in terms of column headings and data types. Is this enough info for you to understand my dilemma?
thank you
Quenril