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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

 

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

Ouch...

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

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.

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
fbrossard
Advocate V
Advocate V

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

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.

Greg_Deckler
Super User
Super User

Sounds like a good item to post in Ideas.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.