cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fcivardi Advocate I
Advocate I

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 IV
Super User IV

Re: Calculated fields

Sounds like a good item to post in Ideas.

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

fbrossard Advocate V
Advocate V

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

Highlighted
fcivardi Advocate I
Advocate I

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 Advocate V
Advocate V

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 IV
Super User IV

Re: Calculated fields

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

fcivardi Advocate I
Advocate I

Re: Calculated fields

...but I still cannot create measures when I "Live Explore" an Analysis Services cube 😞

fbrossard Advocate V
Advocate V

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 Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors