Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
I found this page particularly useful:
https://blogs.msdn.microsoft.com/danrub/2016/03/19/dynamic-topn-ranking-in-power-bi/
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |