Man on couch with computer.

New DAX Functions in Excel Data Models and Power Pivot

Feature deep dive
  • Share on Facebook
  • Share on Twitter
  • Share on LinkedIn

Hi, Office Insiders! I’m Howard Su, a Product Manager on the Excel Team. I’m excited to share with you an improvement to Excel for Windows that allows you to author new DAX functions.

DAX functions

Excel’s Data Model and Power Pivot are tools that help you analyze data across multiple tables, and support a powerful expression language called DAX. Using the power of the Data Model, you can quickly relate multiple data sets to create PivotTables and Pivot Charts. Note that this is the same technology used by Power BI.

You can now use new DAX functions when creating measures in Power Pivot. This provides you new capabilities to analyze, visualize, and report on your data. We are continuing to improve the Data Modeling experience in Excel, so please provide feedback to help us improve your experience.

NOTE: The addition of these DAX functions brings us closer to parity with Power BI Desktop.

How it works

  1. Open an existing worksheet that contains a data model in Excel for Windows. To learn how to load data into the Excel Data Model, you can see Create a Data Model in Excel.
  2. Select Power Pivot > Measures > New Measure.
  3. Enter a new DAX function.

Measure dialog box

4. Create a PivotTable by selecting Insert > PivotTable > From Data Model.

5. Drag the DAX expression to PivotTable Σ Values in the PivotTable Fields list.

 

Below is the list of all the new supported DAX functions:

ACCRINT
ACCRINTM
ALLCROSSFILTERED
AMORLINC
BITAND
BITLSHIFT
BITOR
BITRSHIFT
BITXOR
COALESCE
COLUMNSTATISTICS
COMBINEVALUES
CONTAINSSTRING
CONTAINSSTRINGEXACT
CONVERT
COUPDAYBS
COUPDAYS
COUPDAYSNC
COUPNCD
COUPNUM
COUPPCD
CUMIPMT
CUMPRINC
DB
DDB
DISC
DISTINCTCOUNTNOBLANK
DOLLARDE
DOLLARFR
DURATION
EFFECT
ERROR
FIRSTNONBLANKVALUE
FV
GENERATESERIES
IF.EAGER
INTRATE
IPMT
ISAFTER
ISINSCOPE
ISPMT
LASTNONBLANKVALUE
MDURATION
NAMEOF
NOMINAL
NONVISUAL
NORM.DIST
NORM.INV
NORM.S.DIST
NORM.S.INV
NPER
ODDFPRICE
ODDFYIELD
ODDLPRICE
ODDLYIELD
PDURATION
PMT
PPMT
PRICE
PRICEDISC
PRICEMAT
PV
QUARTER
RATE
RECEIVED
REMOVEFILTERS
RRI
SELECTEDVALUE
SLN
SYD
T.DIST
T.DIST.2T
T.DIST.RT
T.INV
T.INV.2T
TBILLEQ
TBILLPRICE
TBILLYIELD
TREATAS
UNICHAR
USEROBJECTID
UTCNOW
UTCTODAY
VDB
YIELD
YIELDDISC
YIELDMAT

To learn more, see DAX function reference – DAX | Microsoft Learn

Requirements

  • Data Models can only be created in Excel for Windows.
  • Power Pivot must be installed.

Availability

This feature is available to Beta Channel users running Version 2208 (Build 15504.10000 ) or later.

Don’t have it yet? It’s probably us, not you.  

Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.  

Feedback  

We want to hear from you! Please click Help > Feedback to submit your thoughts about this feature. 

Learn what other information you should include in your feedback to ensure it’s actionable and reaches the right people. We’re excited to hear from you!