cancel
Showing results for
Did you mean:
Super Contributor

## TOPN Function - Question/Comment

Okay we all know TOPN doesn't like ties and returns an error

My question is why then if you use TOPN as a filter in a CALCULATE does it include all tied values?

Very misleading - Consider this simple table?

Asset Net

 Asset 1 1000 Asset 2 1000 Asset 3 1000 Asset 4 500 Asset 5 250

And these Measure in order...

```Total Net = SUM ( 'Table'[Net] )
Asset Rank = RANKX ( ALLSELECTED ('Table'[Asset]), [Total Net] )
Top Asset = TOPN ( 1, ALLSELECTED( 'Table'[Asset] ), [Asset Rank], ASC )
Top Asset Total Net = CALCULATE ( [Total Net], TOPN ( 1, ALLSELECTED ('Table'[Asset]), [Asset Rank], ASC) )```

If you only use the above Measures - Top Asset will give you an error while Top Asset Net will give you \$3000

and you'll have no idea how many assets are tied for this \$3000 total (10 at \$300 or 2 at \$1500 etc..)

So we have to use these Measures

```Top Asset Name(s) = CONCATENATEX ( FILTER ( VALUES ( 'Table'[Asset] ), [Asset Rank] = 1 ), 'Table'[Asset], ", ", 'Table'[Asset], ASC )
Top Asset(s) Number = COUNTROWS ( FILTER ( VALUES ( 'Table'[Asset] ), [Asset Rank] = 1 ) )```

And skip the Top Asset Measure to get a better overall picture!

Anyway just my 2 cents worth

Hopefully someone will find this helpful

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: TOPN Function - Question/Comment

@Steve_Wheeler

Thanks for the suggestion (AVERAGEX) - the issue there is that my real table has multiple rows of data per asset per transaction

Hope this makes sense! So it doesn't give the correct result!

Anyway today I ran into another thing that could trip up the TOPN (seems very obvious now)

So the other thing that can trigger this message "Visual can't be displayed..." with the TOPN

When the Calendar rolls over into 2017 (a new year) however there's no data for 2017 (the new year) yet

You may think IF ( ISBLANK (  [TOPN Measure] ), "N/A", [TOPN Measure] ) would fix this but nope!

This patterns however seems to work with the RANKX measure

So for the TOPN Measure you'll have to use

`Top Asset = IFERROR ( TOPN ( 1, ALLSELECTED ( Table[Asset] ), Table[Asset], ASC ), "N/A" )`

Otherwise when your calendar rolls over into the new year but there's no data yet the visual won't display

BTW => this happens in a Table Visual where the first field is YEAR from a Calendar table (and the whole table won't display)

In a Card Visual the same would happen but only if you select 2017 from the YEAR slicer - then it would not display

Hope this makes sense!

6 REPLIES 6
Established Member

## Re: TOPN Function - Question/Comment

I agree that working with RANKX and TOPN (and other DAX!) can get convoluted.  On your questions, as I understand it:

"Okay we all know TOPN doesn't like ties and returns an error"

TOPN doesn't care about ties.  It will always return a table of rows that meet your Top N criteria.  "Top Asset = TOPN(..." will always error as you are passing it a table and not a value?

"My question is why then if you use TOPN as a filter in a CALCULATE does it include all tied values?"

You are passing a table filtered down to the Top N rows back to the CALCULATE, which might include several rows if there is a tie in the rankings or your TOPN is greater than 1.

Super Contributor

## Re: TOPN Function - Question/Comment

@Steve_Wheeleryou are 100% correct on both points! I used the wrong word => "Error" => Clearly not really an error!

However perhaps this picture will show what I mean and what happens when you do have ties

So you get a missing Visual for Top Asset but right next to it Top Asset Net displays the total amount generated by the Top asset?

I meant to say that this leads to ambiguity and uncertainty which can be cleared with the other 2 Measures

one lists the tied assets and the other shows the count of the tied assets

BTW I would've left the report the original way but I saw this happening by pure chance in one month in 2012 in my data

I was just testing out slicers - otherwise if you don't filter the data there are no ties overall

Finally while writing this I'm thinking to even modifiy the Top Asset Net so it actually divides the result by Top Asset(s) Number

basically the 3000 / 3 = 1000 so you see the amount generated by each Top Asset and not the sum of all Top Assets

Hope this makes sense!

Super Contributor

## Re: TOPN Function - Question/Comment

Dear @Sean

https://blogs.msdn.microsoft.com/danrub/2016/03/19/dynamic-topn-ranking-in-power-bi/

Highlighted
Established Member

## Re: TOPN Function - Question/Comment

You seem to dig into the murkier areas of DAX - this is another arcane mystery a bit like the affect of Sort By Column on ALL filters...

Sorry, I missed your point that your original 'Top Asset' measure actually returns a valid value if you don't have ranking ties - you were absolutely right but I didn't see how that should happen.   Re-reading the doco,  I see now that TOPN return a table of rows and, if that table has one row (i.e. no ties) AND one column, then the measure will not error out.

Thinking it through aloud, the following variation errors out as now expected, with "The expression refer to multiple columns..." - one row, but more than one column.  Though more inefficient with the unneeded columns, it will work if wrapped in a CALCULATE, SUMX, CONCATENATEX etc. that can handle the extra columns...

`Top Asset = TOPN ( 1, ALLSELECTED( 'Table' ), [Asset Rank], ASC )`

I like your CONCATENATEX measure to list out joint top-ranked items, and agree that averaging the Top Asset Value is more accurate.  I came up with this version, though using just 'Table'[Net] in the ALLSELECTED would presumably be more efficient:

`Top Asset Value =AVERAGEX (    TOPN ( 1, ALLSELECTED ( 'Table' ), 'Table'[Net], DESC ),    'Table'[Net])`

Thanks for stretching my brain.  For me the lesson is to think of TOPN as TOPNROWS, and always wrap it in an aggregator for safety.

It's not an error, but it IS obscure I think - I wish these wrinkles were documented as Remarks on the related MSDN page or somewhere else central...

Super Contributor

## Re: TOPN Function - Question/Comment

@Steve_Wheeler

Thanks for the suggestion (AVERAGEX) - the issue there is that my real table has multiple rows of data per asset per transaction

Hope this makes sense! So it doesn't give the correct result!

Anyway today I ran into another thing that could trip up the TOPN (seems very obvious now)

So the other thing that can trigger this message "Visual can't be displayed..." with the TOPN

When the Calendar rolls over into 2017 (a new year) however there's no data for 2017 (the new year) yet

You may think IF ( ISBLANK (  [TOPN Measure] ), "N/A", [TOPN Measure] ) would fix this but nope!

This patterns however seems to work with the RANKX measure

So for the TOPN Measure you'll have to use

`Top Asset = IFERROR ( TOPN ( 1, ALLSELECTED ( Table[Asset] ), Table[Asset], ASC ), "N/A" )`

Otherwise when your calendar rolls over into the new year but there's no data yet the visual won't display

BTW => this happens in a Table Visual where the first field is YEAR from a Calendar table (and the whole table won't display)

In a Card Visual the same would happen but only if you select 2017 from the YEAR slicer - then it would not display

Hope this makes sense!

Super Contributor

## Re: TOPN Function - Question/Comment

Alternatively to quickly deal with the TIES just wrap the TOPN ( ) with a FIRSTNONBLANK ( ) like described here

http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 279 members 2,831 guests
Recent signins: