Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

TOP 2 Users By Report

I have log of report users.  Each time a user runs a report, a log entry of the report name, their username and date/time they accessed the report are recorded in a log.  From that log I need to know the top 2 users who access each report the most.  

 

I have figured out how to summarize how many times ALL users have accessed each report with a simple COUNTROWS DAX command.  The actual access counts of each report by user are displayed.  However, I don't to see ALL users. I only want to see the top 2 for each report.

 

The output currently looks like this:

 

 

This is how it should look:

 

 

Thanks in advance!

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Here's the solution.  I had to pick another column:

     

     

    And then I added the column and a page filter.  I selected the top 2.   That worked.

     

     

    Thank you for your help.  You steered me in the right direction.

     

    ---MW

5 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Anonymous 

     

    You can add a new column with RANKX:

    Column = 
    RANKX(filter(all(table),table[Name]=earlier(table[Name])),table[ReportViews],,ASC,DENSE)

    then you can add that column to your visual filter section and filter to show 1 and 2.

     

    Can you share a sample of your data in a text or table format to be able to copy and paste that? then we can use your data to prepare better solution.

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for the reply.  The formula works to show a count, which I already had.  But when applying a filter, it doens't return only the top 2 of each user as expected.   

       

      Note that the "reportedview" column is a calculated column.  There are thousands of entries per report.

       

      Here's some sample data:

       

       

      NAMEReportViewsUSERNAME
      AccountLeadOwner129IIC2\svcAppPool2G
      ASSETCHANGER-TFR-TXN10IIC2\ccrespo
      AssetDiscrepancyReport240IIC2\svcSQLRSAP05
      Audit_Report42IIC2\svcSQLRSAP05
      Audit_Report_Rollup18IIC2\egonzalez
      Audit_Report_Rollup42IIC2\svcSQLRSAP05
      BookedReport2IIC2\kanti
      BookedSalesReport1IIC2\svcAppPool2G
      Cash App Recon Details Report77IIC2\aruiz
      Cash App Recon Details Report2IIC2\vambrose
      Commissions6IIC2\extrbravo
      Commissions18IIC2\kanti
      Commissions1IIC2\svcAppPool2G
      COOShipmentTracking1492IIC2\svcAppPool2G
      CORPSMAP05 SSRS Report1IIC2\!ccrespo
      Current and Prior Year History with Parameters30IIC2\egalan
      Current and Prior Year History with Parameters4IIC2\mdold
      Current and Prior Year History with Parameters35IIC2\rdomke
      Current and Prior Year History with Parameters82IIC2\smontelius
      Current Balance22IIC2\dhernandez
      Current Balance25IIC2\egalan
      Current Balance5IIC2\mmagee
      • VahidDM's avatar
        VahidDM
        Super User
        4 years ago

        Anonymous 

         

        Add a new column with this code:

        Rank = 
RANKX(filter(all('Table'),'Table'[Name]=earlier('Table'[Name])),'Table'[ReportViews],,DESC,DENSE)

         

        Output:

         

         

        then create a visual and add the new column (Rank) to the filter pane for that visual and set to show the 1 and 2.

        Out put:

         

         

        Download the file: https://gofile.io/d/TOkKKD

         

        If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
        Appreciate your Kudos!!
        LinkedIn:         www.linkedin.com/in/vahid-dm/