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

Calculated fields

When I connect to an Analysis Sevices database in "Explore live" mode, I'd like to have the possibility to define at least some simple calculations (like ratios). It would be nice to be able to define MDX (or DAX) calculated members - without having to import the data.

8 REPLIES 8
Super User
Super User

Re: Calculated fields

Sounds like a good item to post in Ideas.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


fbrossard Member
Member

Re: Calculated fields

If you use Analysis Service on top of you SQL DWH, the bette way is to add calculated measure in your cube in ordre to expose this information for all your the users regardless of the browser (Excel ou PBI).

Bu I agree, from a general point of view it would be great to add calculated measures when using direct query no matter the data source (sql, ssas)

 

https://ideas.powerbi.com/forums/265200-power-bi/suggestions/10435572-create-dax-measures-fields-and...

fcivardi Regular Visitor
Regular Visitor

Re: Calculated fields

I totally agree, business logic (i.e. calculations) should be defined in the cube... but sometimes you want to try a new calculation on the fly, before consolidating it. Thanks for the link, I just voted it.

fbrossard Member
Member

Re: Calculated fields

Hi @fcivardi@Greg_Deckler,

 

Now with Power Bi Desktop you can add calculated measures when using Direct Query mode.

See Power BI January updates http://goo.gl/WpNNzN

 

Super User
Super User

Re: Calculated fields

@fbrossard - Leave it to the Power BI Developers to be all over it!!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


fcivardi Regular Visitor
Regular Visitor

Re: Calculated fields

...but I still cannot create measures when I "Live Explore" an Analysis Services cube :-(

fbrossard Member
Member

Re: Calculated fields

Ouch...

seems to work only on SQL DataSource, I've tried SSAS Tabular and Multidim and it doesn't work for both. 

greggyb New Contributor
New Contributor

Re: Calculated fields

Direct Query and Live Explore are two different technologies.

 

Direct Query involves a DAX -> SQL translation layer. Allowing defined measures is simply a case of storing metadata in the .pbix, which is the DAX measure definition. This measure is translated at run-time into SQL and fired off as part of the query against the SQL source.

 

Live Explore is a method of read-only access to a SSAS cube. SSAS can deal with DAX natively, and the queries against Tabular need no translation. DAX->MDX can be done by SSAS Multidimensional. MDX->DAX can be done by Tabular (for pivot tables and similar).

 

As these are two different pieces of functionality, it shouldn't be expected that they are developed in sync.

 

Allowing measures to be defined against a SSAS source has two primary methods. We could allow write access to the cube from PBI, allowing measures to be added directly to the cube itself, such that they would be accessible to other programs accessing that cube. This seems to me to be an unlikely feature.

The other option is to define the measure metadata in PBI, similarly to Direct Query and allow that to be passed as part of the query string (DAX queries do allow in-line measures to be defined). I'm not sure how likely this would be as a feature. Technically it should be much less difficult than allowing editing of the cube directly. Either way, as a different piece of functionality, there's no reason to expect that Direct Query development should have any major impact on Live Explore development.