Workaround for the "Analyze with Excel" Direct Query bug

[rayishome]

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!

[ankitpatira]

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

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]

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

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.

[Seth_C_Bauer]

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

[rayishome]

It's occuring with sql server direct query.

[Seth_C_Bauer]

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

[rayishome]

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