Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Resolver I
1 year ago

TOPN by Date

I'm trying to caluclate the top 7 Gross Adds by month. So I use the filter topn in the filter section which works fine.   But I want to have another table next to it that adds 364 days to each top 7 dates, so as to project the future year top dates but on the same weekday.  I have a slicer that is Month Year. 

So I try a measure that results in error that "expression refers to multiple columns".  

TopFutureDates = 
        TOPN ( 
            7, 
            ALLSELECTED( 'Subscriber Activity'), 
         [Gross Adds],
            DESC 
        )

 So I tried:  

TopFutureDates = CALCULATE ( 
    SUMX(VALUES('Calendar'[Calendar Date]),[Gross Adds]),  
    KEEPFILTERS ( 
        TOPN ( 
            7, 
            ALLSELECTED( 'Subscriber Activity'), 
         [Gross Adds],
            DESC 
        ) 
    ) 
)

This results in a sum total of the whole month not top 7.  If I include the date in the Columns pane it shows all of the dates for the whole month.  I'm wanting it to return the top 7 values along with the date.  Then add 364 days to each date it finds.  Thanks!  

TopN

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Assuming your data model is wired with Calendar as dimension and Subscriber Activity as fact

     

    TopFutureDates = 
        TOPN ( 
            7, 
            ALLSELECTED( 'Calendar'[Calendar Date]), 
         [Gross Adds],
            DESC 
        )

    Or encase it with SELECTCOLUMNS

    • BrianNeedsHelp's avatar
      BrianNeedsHelp
      Resolver I
      1 year ago

      lbendlin I get error: Table of multiple values was supplied where a single value was expected when using: 

       

      TopFutureDates = 
        TOPN ( 
            7, 
            ALLSELECTED( 'Calendar'[Calendar Date]), 
         [Gross Adds],
            DESC 
        )

      Tried this:   

      TopFutureDates = 
       SELECTCOLUMNS( TOPN ( 
            7, 
            ALLSELECTED( 'Calendar'[Calendar Date]), 
         [Gross Adds],
            DESC 
        ) )

      "Too few arguments were passed to the SelectColumns function.  The minimum argument count for the function is 2."

       

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Once you  have your top 7 rows, what do you want to do with them next?

  • BrianNeedsHelp's avatar
    BrianNeedsHelp
    Resolver I
    1 year ago

    I finally got the first part to work!  

     

    TopDates = 
        CONCATENATEX(TOPN ( 
            7,
            VALUES( 'Calendar'[Calendar Date]), 
         [Gross Adds],
            DESC 
        ), [Calendar Date],CONCATENATE ( "", UNICHAR ( 10 ) ))

     

    Now for future dates I got it to work with: 

     

    TopFutureDates = 
        CONCATENATEX(TOPN ( 
            7,
            VALUES( 'Calendar'[Calendar Date]), 
         [Gross Adds],
            DESC 
        ), [Calendar Date]+364,CONCATENATE ( "", UNICHAR ( 10 ) ))

     

     But is there a way to display the Dates and the Gross Adds in the [TopDates] measure above?  Right now I've only been able to display either or in the [TopDates] measure.  

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      Measures must return a single scalar value. You can keep using CONCATENATEX but then you need to unpack that text result again yourself.  In this case Line Feed is not a useful string separator.