Forum Discussion

Quenril's avatar
Quenril
Icon for Resolver I rankResolver I
2 years ago
Solved

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. 

 

DateClient nameLead ConsultantConsultant Attendee 1Consultant Attendee 2Consultant Attendee 3Consultant Attendee 4Cross Practice?
21/04/24Client AMiss ScarlettMiss PeacockColonel Mustard  Y
21/04/24Client BMiss PeacockColonel MustardProfessor Plum  Y
22/04/24Client CReverend Green    N
23/04/24Client 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 what does your raw data look like? Is that screen shot present your raw data table?

    • Quenril's avatar
      Quenril
      Icon for Resolver I rankResolver 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 

  • Quenril you have to redact your sample data and share it, no need to share the real data. If you cannot give how your raw data looks like (again just create a sample data), share that for a quick solution.