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
Anonymous
Not applicable

TOPN function doesn't allow me to quote a table

Hi, I try to create a topn measure to get the sum of  of top 5 KPI achievement %, however, it looks like the TOPN function doesn't allow me to quote a table. Usually after typing the ' sign (see below), Power BI will give me a list of tables, but nothing comes out with topn function here. I also get a warning of syntax error if I try to input table name manually. 

 

Any help will be highly appreciated. 

 

topn = calculate([total kpi], topn(5,'

 

 

8 REPLIES 8
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

I have no access to your file, please upload it to One drive and share the link here.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thank you for follow up. I can not share the file with you via onedrive. I have a company account, which doesn't allow me to share onedrive file with people outside the organization.

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

As tested, it works on my side.

Measure = CALCULATE(SUM([value]),TOPN(5,Table1,[value]))
or
Measure = CALCULATE(SUM([value]),TOPN(5,'Table1',[value])) or Measure = CALCULATE(SUM([value]),TOPN(5,ALL(Table1),[value]))

2.png

3.png

Please share a simple data for me to test.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thank you for the prompt feedback. I suspect the issue may relate to my computer language setup or power bi version. Is there a way for me the send you the data and topn function as an attachment rather than a printscreen?

 

 

 

Hi @Anonymous 

My pbix

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Here is the URL to the pbix sampel file.

 

https://app.powerbi.com/groups/me/reports/ac10cde6-be0b-4a8d-9662-242ddc34ceb6?ctid=a1fdea56-3201-4d...

 

I just tried inputting the same measure on a colleagues's power query module (in Excel), which seems to work. I wouldn't like to know why it doesn't work with my power bi desktop.

 

Thanks!

Anonymous
Not applicable

Hi, just to let you know that I have installed the latest power bi desktop and open your file from there, I got the same error message with your function.

 

 

Stachu
Community Champion
Community Champion

I've had PowerBI act funny becaue of spaces once - try using DAX formatter to format your code, maybe it will help:

https://www.daxformatter.com/



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

Top Solution Authors
Top Kudoed Authors