cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rayishome Regular Visitor
Regular Visitor

Workaround for the "Analyze with Excel" Direct Query bug

When connecting to a live data source  (Direct Query Mode) and using the "Analyze with Excel" the numeric values that are present in the data and aggretable in Power BI's interface can not be used in Excel to aggregate, which makes this feature uselss as a pivot table. 

 

Is there a workaround other than Importing the Data and creating measures.  Power BI only has a 10G limit on data!

8 REPLIES 8
ankitpatira Super Contributor
Super Contributor

Re: Workaround for the "Analyze with Excel" Direct Query bug

@rayishome Numerics and aggregates present in your data source cannot be used in Excel to aggregate even when you Import data in power bi. That is standard in Pivot Table. You need to explicitly create measures in order to use them as aggregates in Pivot Table. Connecting via DirectQuery will still allow you to create measures in power bi desktop and once report is published you can use those measures under Values section in Pivot Table.

rayishome Regular Visitor
Regular Visitor

Re: Workaround for the "Analyze with Excel" Direct Query bug

That would be great, but the ability to creat measures on direct queries is disabled, well not really disabled , but if you creat one it deletes it.
ankitpatira Super Contributor
Super Contributor

Re: Workaround for the "Analyze with Excel" Direct Query bug

@rayishome really, I haven't came across that before. I have used directquery to sql server, created measures in desktop, published to service and use analyze in excel. What is your data source ? And where does it delete it. Do you see them appearing in power bi service ?

rayishome Regular Visitor
Regular Visitor

Re: Workaround for the "Analyze with Excel" Direct Query bug

MS hasn't built support yet for measures on direct queries so the measure object will show up for a few seconds, but doesn't let you configure it.
Super User
Super User

Re: Workaround for the "Analyze with Excel" Direct Query bug

@rayishome This might just be a "naming" issue, but you can create measures and caculated columns when using Direct Query (Direct Query is the method to connect to a SQL Database)

It does not work in using Live Connection

(Live Connection is the method to connect to a SSAS model)

 

I'm assuming your issues revolve around SSAS? or you are experiancing this with connecting to a database?


Near SE WI? Join our PUG Milwaukee Brew City PUG
rayishome Regular Visitor
Regular Visitor

Re: Workaround for the "Analyze with Excel" Direct Query bug

It's occuring with sql server direct query.
Super User
Super User

Re: Workaround for the "Analyze with Excel" Direct Query bug

@rayishome I get it now. You are saying that you can't build a measure in Excel - correct?

I just tested this with a measure I created in PBI Desktop on top of a direct query, published to the Service, Analyzed in Excel. and it worked as expected.


Near SE WI? Join our PUG Milwaukee Brew City PUG
rayishome Regular Visitor
Regular Visitor

Re: Workaround for the "Analyze with Excel" Direct Query bug

Just downloaded the latest version and the measures seem to work. Thanks

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)