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...
  • 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

VahidDM's avatar
VahidDM
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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/

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      VahidDM 

       

      Unfortunately that DAX forumla didn't work. I get a "circular dependency was detected" error.