Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sean
Community Champion
Community Champion

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 11000
Asset 21000
Asset 31000
Asset 4500
Asset 5250

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! Smiley Happy

Anyway just my 2 cents worth Smiley Happy

Hopefully someone will find this helpful Smiley Happy

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@Anonymous

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! Smiley Happy 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! Smiley Happy

View solution in original post

6 REPLIES 6
Baskar
Resident Rockstar
Resident Rockstar

Dear @Sean

 

Can u please check this link , it give u some idea to achieve your goal.

 

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

 

 

Anonymous
Not applicable

 @Sean,

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.

 

 

Sean
Community Champion
Community Champion

@Anonymousyou 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

TOPN Question 2.png

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

 

TOPN Question.png

 

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! Smiley Happy

 

Anonymous
Not applicable

@Sean,

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 ( 1ALLSELECTED ( '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...

Sean
Community Champion
Community Champion

@Anonymous

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! Smiley Happy 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! Smiley Happy

Sean
Community Champion
Community Champion

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

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.