Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have some data about reports and when they are run as well as some other details. I'm trying to create a new rank column based on how many times a report is run in the given context so that eventually I can filter by top 10 or bottom 10 reports based on rank.
I'm not sure if I'm doing this correctly but I figured I would need to group the records for each report since it is fine grained data and a report can be run multiple times each day.
Rank = RANKX('ReportStatistics', GROUPBY('ReportStatistics','ReportStatistics'[RunDate],"Count",COUNTA(ReportStatistics[ReportName])), , DESC)
Currently the above give the error:
Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().
You are trying to rank the report names by the number of times run, within a given day? Is that correct?
You could do something like:
Rank = CALCULATE ( RANKX ( ALL ( 'ReportStatistics'[ReportName] ), CALCULATE ( COUNTROWS ( 'ReportStatistics' ) ) ), VALUES ( 'ReportStatistics'[RunDate] ) )
The "grouping" comes from
VALUES ( 'ReportStatistics'[RunDate] )
So essentially, you are using a RANKX function inside a CALCULATE, with VALUES() being used to create a context for ranking.
How would I make it work for a date range? Currently the rank acts the same as a normal count:
Did you try viewing by [RunDate]? If you use [RunDate] in the table Rows (before [ReportName]), what does the ReportName ranking look like?
Another method - if you were to filter this table for a single date (via [RunDate]), what does the ranking look like?
Maybe I am unclear on how exactly you are hoping to "group" the report ranking.
My apologies if I was unclear before. If I select a date range, let's say last 30 days, I would expect to see the report names, # of times run, and then rank where rank 1 is the report run the most and vice versa.
So in the table screenshot before, the report run 112 times would have rank 1 and all of those reports are limited to the past 30 days.
I see. In that case, you shouldn't need any special grouping, and maybe that's where the confusion is coming from.
You should be able to use the following measure:
Ranking =
RANKX (
ALL ( 'ReportStatistics'[ReportName] ),
CALCULATE ( COUNTROWS ( 'ReportStatistics' ) )
)
To filter by date, use the [RunDate] field in a slicer. The slicer will allow you to select a date range, and the measure should adjust to rank according to that selection.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
96 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |