Forum Discussion
TOPN Function - Question/Comment
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! :smileyhappy: 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! :smileyhappy:
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 ( 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...
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! :smileyhappy: 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! :smileyhappy:
Alternatively to quickly deal with the TIES just wrap the TOPN ( ) with a FIRSTNONBLANK ( ) like described here :smileyhappy:
http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/