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 for each activity type. For each activity type I have a fact table with counts - screenshot example...
Need Help
  • Quenril's avatar
    Quenril
    2 years ago

    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 dateConsultant NameDivision
    01/01/2020Consultant ABusiness Tranformation
    01/04/2022Consultant BFinance
    08/04/2022Consultant CBoard
    15/04/2023Consultant DBoard
    07/06/2023Consultant EBusiness Tranformation
    31/10/2023Consultant ADigital & Technology
    31/10/2023Consultant DSales & Marketing
    01/11/2023Consultant FDigital & Technology

     

    Thank you

    Becky

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

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.

 

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

    Hi 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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      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.