topic Re: TOPN by Date in DAX Commands and Tips

TOPN by Date

BrianNeedsHelp — Sun, 27 Oct 2024 05:13:48 GMT

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!

Re: TOPN by Date

lbendlin — Sun, 27 Oct 2024 12:03:19 GMT

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

Re: TOPN by Date

BrianNeedsHelp — Sun, 27 Oct 2024 17:19:08 GMT

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

Re: TOPN by Date

lbendlin — Sun, 27 Oct 2024 17:59:02 GMT

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

Re: TOPN by Date

BrianNeedsHelp — Sun, 27 Oct 2024 18:45:27 GMT

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.

Re: TOPN by Date

lbendlin — Sun, 27 Oct 2024 19:22:56 GMT

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.