Forum Discussion

PorterHaus's avatar
PorterHaus
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 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().

Need Help

5 Replies

  • jmalone's avatar
    jmalone
    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. 

    • PorterHaus's avatar
      PorterHaus
      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's avatar
        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.