Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
rayishome
Resolver I
Resolver I

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
Community Champion
Community Champion

@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.

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.

@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 ?

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.

@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?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

It's occuring with sql server direct query.

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors