topic Re: TOP 2 Users By Report in DAX Commands and Tips

TOP 2 Users By Report

Anonymous — Tue, 30 Nov 2021 23:26:46 GMT

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!

Re: TOP 2 Users By Report

VahidDM — Tue, 30 Nov 2021 23:41:08 GMT

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/

Re: TOP 2 Users By Report

Anonymous — Wed, 01 Dec 2021 00:52:09 GMT

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:

NAME	ReportViews	USERNAME
AccountLeadOwner	129	IIC2\svcAppPool2G
ASSETCHANGER-TFR-TXN	10	IIC2\ccrespo
AssetDiscrepancyReport	240	IIC2\svcSQLRSAP05
Audit_Report	42	IIC2\svcSQLRSAP05
Audit_Report_Rollup	18	IIC2\egonzalez
Audit_Report_Rollup	42	IIC2\svcSQLRSAP05
BookedReport	2	IIC2\kanti
BookedSalesReport	1	IIC2\svcAppPool2G
Cash App Recon Details Report	77	IIC2\aruiz
Cash App Recon Details Report	2	IIC2\vambrose
Commissions	6	IIC2\extrbravo
Commissions	18	IIC2\kanti
Commissions	1	IIC2\svcAppPool2G
COOShipmentTracking	1492	IIC2\svcAppPool2G
CORPSMAP05 SSRS Report	1	IIC2\!ccrespo
Current and Prior Year History with Parameters	30	IIC2\egalan
Current and Prior Year History with Parameters	4	IIC2\mdold
Current and Prior Year History with Parameters	35	IIC2\rdomke
Current and Prior Year History with Parameters	82	IIC2\smontelius
Current Balance	22	IIC2\dhernandez
Current Balance	25	IIC2\egalan
Current Balance	5	IIC2\mmagee

Re: TOP 2 Users By Report

VahidDM — Wed, 01 Dec 2021 01:11:25 GMT

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/

Re: TOP 2 Users By Report

Anonymous — Wed, 01 Dec 2021 16:11:50 GMT

VahidDM

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

Re: TOP 2 Users By Report

Anonymous — Wed, 01 Dec 2021 16:18:52 GMT

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