Development for Power Pivot for Excel seems to have stopped around 2016. TREATAS got added as a DAX function that works, but it never got added to Intellisense, and that was nearly 3 years ago.

Today, the two measure editors (still no idea why there are different ones) are a very VERY poor substitute for the fantastic DAX editor in Power BI Desktop. It is a real pain to properly tab and indent in the Excel Measure editor(s).

In the last year there have been about a dozen new DAX functions and several updated functions per https://docs.microsoft.com/en-us/dax/new-dax-functions but NONE of those are in Excel. I understand not all of them should be. APPROXIMATEDISTINCTCOUNT would have no relevance in Excel, but CONVERT, REMOVEFILTERS, CONTAINSSTRING, DISTINCTCOUNTNOBLANK, and others would all be useful to have.

We also cannot create tables in Excel using Measures.

Cannot enable bi-directional filtering. The CROSSFILTER function only helps with calculations, not slicers.

It seems really weird that Power Query in Excel is keeping pace with PQ in Power BI Desktop, other than missing many data sources, but internally functionality is pretty much on par, from functions to the UI.

Why is Power Pivot being seemingly left to wither on the vine?

Data models often include helper columns, which can be hidden from pivot table available fields using 'hide from client tools'.

However, if the end-user wants the underlying data and uses the drill-down functionality of a pivot table (double-clicking one of the values to open a new sheet with underlying data), they are presented with every column in that table in the data model.

The 'hide from client tools' option works to hide them from available fields in the pivot tables, but it would be nice if it also removed those columns from the new sheet of raw data created when the user double-clicks a Value in the Pivot table to get a full data set.

If it doesn't make sense to combine this functionality into the current 'Hide from client tools', then we could add a new one 'Hide from data drill-downs'.

Thoughts everyone?!

(I'm also interested in how my peers may have worked-around this, as I have 50+ columns of data and users sometime struggle navigating to the data they need.)

With all the queries on the internet, we assume there is no built-in option to prevent pivot tables from overwriting data beneath the table during refresh and expansion. Is that correct?

Given that Excel has options for 'inserting' rows, why in the world would Microsoft NOT provide a pivot table expansion/contraction option for inserting or deleting rows from within the refreshed table without overwriting data beneath or to the right of the table?

It is also interesting that inserting rows into a regular Excel table, using the TAB key in the last row, can add rows by expanding the table without overwriting the data beneath. Yet, it will fill in any blank rows first before shifting down the data rows beneath the table.

Given the many requests for this option, we are surprised that Microsoft has not responded to this request.

Currently, the only way to add a PivotTable or PivotChart that is based on the Data Model is to use the "PivotTable" button in the Home tab of the Power Pivot window. This button should also appear in the Power Pivot tab in Excel's Ribbon.

When I work with Power Pivot, I'll do all of the Data Model work first, using only a few PivotTables for debugging purposes, and once I am confident that everything is correct, then I'll go crazy with making all of the final PivotTables and PivotCharts. This involves a lot of back-and-forth with the Power Pivot window. Also, when I think of adding a PivotTable, I think of where it is going, not what it is based on, and so I mistakenly go to the Excel window to try to add a PivotTable, only to go back to the Power Pivot window.

I'd like to think that there are more people that work in a similar way.

There are many aggregation iterator functions in DAX (SUMX, MINX, etc.). Their concept is the same: evaluate an expression for each row of a table and aggregate the results.

Instead of blowing up the function list with functions like these, there should either be a generic aggregation iterator function that accepts the aggregation function as an argument, or a generic iterator function that covers the iteration step but not the aggregation step (i.e. it returns a list of values to be fed to the "normal" aggregation functions).

The functions would look like this:
AGGREGATE(AggregationFunction, Table, Expression)
X(Table, Expression)

So instead of this:
SUMX(Table, Expression)
MINX(Table, Expression)

We would have this:
AGGREGATE(SUM, Table, Expression)
AGGREGATE(MIN, Table, Expression)
SUM(X(Table, Expression))
MIN(X(Table, Expression))

Once these functions are introduced, then SUMX, MINX, etc. can be deprecated.

The AGGREGATE function would also allow the aggregation type to be easily toggled/selected (using slicers and SWITCH) without repeating the Expression argument for each desired aggregation type and without using calculation items or calculation groups (which are still years away from being included in Power Pivot, if ever).

Many of the aggregation functions in DAX come in pairs. For example, SUM and SUMX, or MIN and MINX. The "not X" functions aggregate a single column and the "X" functions evaluate an expression for each row of a table and aggregate the results.

Using SUM as an example, why not just have SUM act like both SUM and SUMX and then SUMX can be deprecated? If the 1st argument is a column name, then it should act like SUM. If the 1st argument is a table, then it should act like SUMX.

This would greatly declutter the list of functions, both now and in the future, which, in turn, makes the language less intimidating, especially for beginners. If you are worried that multiple syntaxes will cause confusion, just remember that a similar idea is already used in the ALL function.