How to create a measure in Power Pivot when using Tabular Model as data source?

[Anonymous]

We have a tabular model and originally had a Power BI report. Unfortunately because Power BI cannpot export to Excel with any formatting, we have decided to develop the report in Excel using Power Pivot.

But we have just discovered that it does not seem possible to create the measures we need in the Excel pivot table! In the Power BI report we created dozens of measures. Is there no way to create measures in Excel when the data source is a tabular model?

If we cannot do this, then we will need to add all those measures to the tabular model and republish the model.

This seems an extremely high-maintenance way of going about things. This would mean any time we needed a new measure on the Excel report, we would need to add it to the tabular model and deploy it to the SSAS server. This seems overkill when I believe this should be a simple matter for a report developer to perform.

Am I missing something?

[amitchandak]

@Anonymous , I am assuming Analyze in excel and Curate featured tables for Excel did not suit to your requirements.

[FrankAT]

Hi @Anonymous 

you can create measure in Excel (Power Pivot) like in Power BI Desktop (see figure):

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

After I connect to the SSAS tabular model and place the pivot table into my spreadsheet, I tried clicking Power Pivot | Measues | New Measure. It gives me this dialog.

[Icey · Accepted solution]

Hi @Anonymous ,

Try to create a MDX Calculated measure.

Reference: Calculated Member and Measures in Excel 2013

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

That works, thank you. But it does not seem to accept a DAX measure definition. Will it only allow for MDX measure to be defined? 

[Icey]

Hi @Anonymous ,

Sorry, I don't know much about this part. So I can't give you a definite answer. But I have some inferences:

Based on my test, if we connect to SQL Server database, we can create a new measure.

But when connecting to SSAS Tabular model, we can't. The only way for me is to create a MDX calculated measure. It may work like report level measures in Power BI when connecting to SSAS Tabular model.

It is suggested to create a post on the Excel Community to verify my conjecture above.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

@Anonymous 

As far as I know, you can create a model in Excel just like you can in PowerBI. I believe the trick is you have to specify that you wish to load queries into a "model" in Power Query. 
I'm sorry I can't be more specific (I'm typing from my phone) but in principle the option is readily available. 
also, to create measures the syntax requires a ":=" as opposed to the simple "=" we use in PBI

[Anonymous]

I have opened a new Excel document, clicked Get Data, and selected my SSAS database. Is this dialog below where I can select to add the data to the Data Model? Is this where you mean? For me, it is greyed out.