cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeanWang Frequent Visitor
Frequent Visitor

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
Community Support Team
Community Support Team

Re: TOPN function doesn't allow me to quote a table

Hi @JeanWang 

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.

JeanWang Frequent Visitor
Frequent Visitor

Re: TOPN function doesn't allow me to quote a table

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?

 

 

 

Community Support Team
Community Support Team

Re: TOPN function doesn't allow me to quote a table

Hi @JeanWang 

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.

JeanWang Frequent Visitor
Frequent Visitor

Re: TOPN function doesn't allow me to quote a table

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!

JeanWang Frequent Visitor
Frequent Visitor

Re: TOPN function doesn't allow me to quote a table

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 Super Contributor
Super Contributor

Re: TOPN function doesn't allow me to quote a table

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!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: TOPN function doesn't allow me to quote a table

Hi @JeanWang 

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.

JeanWang Frequent Visitor
Frequent Visitor

Re: TOPN function doesn't allow me to quote a table

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.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 384 members 3,248 guests
Please welcome our newest community members: