TopN error when trying to show ten rows in a table

[awilliams]

Hi guys, 

Try = TOPN(10,ga1,ga1[Candidate Registrations], DESC)

May be a simple one here but im getting the following error when trying to use the TopN DAX expression to only show the first ten rows of a table: 

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

The syntax highlighter is not showing up any errors, so not sure why this won't work...

Excuse the simplicity as I am new to PowerBi and DAX, any help would be greatly appreciated!

Thanks

[v-haibl-msft]

@awilliams

TOPN returns the top N rows of the specified table. So you need to create a new table.

Or you can sort the column of ga1[Candidate Registrations] and then keep bottom 10 rows in Query Editor.

Best Regards,

Herbert

[awilliams]

Thanks for your reply @v-haibl-msft. Unfortunately I don't want to remove rows from one column. I just simply want to show only the top ten results of a table, rather than the entire results.

[ronnoc]

I found this page particularly useful:

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

[v-haibl-msft]

@awilliams

If above resolutions are not you wanted, you can try add a rank column with following formula.

Rank = RANKX( gal, gal[Candidate Registrations])

Then type the top number you wanted in visual level filter.

Best Regards,

Herbert

[Anonymous]

So, you are writing a measure, and measures must always return a single (scalar) value.  TOPN( ) returns N rows... and you have not specified what you want to "happen" to those rows.

Something like   = CALCULATE( SUM(ga1[value]), TOPN(10, ga1, ga1[Reg], DESC) )  should do... something... I think? 🙂

Maybe easier to understand a   = IF (RANKX(ga1, ga1[Reg]) <= 10, [My Measure], BLANK() ) but should do the same.

Your formula would work if you use the "Calculate Table" function to create a brand new table of just the top 10, but that probably isn't what you want.