Forum Discussion

Frequent Visitor

8 years ago

Grouping in Rankx

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

Resolver III

8 years ago

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.

Frequent Visitor

8 years ago

How would I make it work for a date range? Currently the rank acts the same as a normal count:

jmalone
Resolver III
8 years ago
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.
- PorterHaus
  Frequent Visitor
  8 years ago
  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.
  - jmalone
    Resolver III
    8 years ago
    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.

Related content