Category Archives: DAX

Testing a DAX measure using Excel functionality – XIRR example

Power Pivot’s DAX is missing many of the Excel functions we’ve come to take for granted, but it’s often possible to simulate them using pure DAX, see here for my attempt at a DAX Percentile function.

Even if the logic behind such attempts is sound (or not), the result is likely to be judged against Excel’s version. Also, in many cases, due to the limitations of DAX, crafting an acceptable solution may not be possible. The attached workbook shows a method to either test, or replace, such a DAX measure using existing Excel functionality.

The example attempts to emulate the XIRR function in DAX and test the result against Excel’s version.

It uses a VBA User Defined Function which can “hook into” a pivot table which has been converted to static  CUBE functions. The UDF can then extract the “cross-tab” filters for each cell and generate a DAX Query call via ADO to get the filtered sub-set from the Power Pivot model. It then applies the Excel XIRR function to this data.

The DAX version of XIRR uses a brute force method to pass a range of rates to a NPV calculation, picking the average of the lowest return(s). In the example there are 3000 odd rates (-100% to 200% with one place of decimals so  …10.1%,10.2% …).

A smaller range would result in a faster measure, but be more likely to fail when the yield is outside that range. Likewise, increasing the range of rates, for example, by increasing the number of decimal points, would add to the time taken to calculate.

So, be warned, this is not a generic solution, it would need to be adjusted for the real-world yields that are likely to be encountered and perhaps modified to allow pre-calculation at lower levels and/or allow for restricted “likely-rate” ranges as a first pass, only going to a wider, slower range for outliers.

You’ll notice a definite lag when filtering the example, made worse by the use of CUBE functions which are not as efficient as single Pivot Table callbacks. So, irregardless of how correct or not the measure is, it’ll be a performance non-runner unless properly tuned. In fact, using the callback to Excel method as the solution rather than as the test, may end up as the only practical way to do this.

Here’s the Excel 2013 version, and here’s an Excel 2010 one.

NOTE: CUBE formulas used with pre-2013 PP Models reference “PowerPivot Data”, this will not be updated to “ThisWorkbooksDataModel” when the workbook is subsequently auto-upgraded to the Excel 2013 Data Model.

Below, is the [xirr] measure, with one supporting [npv] measure and a supporting calculated column. There’s also a calculated column [StandardisedDate] for use by the VBA UDF.

xirr:=IF(
ISBLANK(SUM(Cashflow[Cashflow])),
BLANK(),
CALCULATE(
AVERAGE(RATES[Rate] ),
FILTER(
RATES,
ABS(Cashflow[npv])
=
MINX(
RATES,
ABS(Cashflow[npv])
)
)
)
)

npv:=SUMX(
SUMMARIZE(
Cashflow,
Cashflow[DaysSince],
"grouped CashFlow",
sum(Cashflow[Cashflow])
),
[grouped CashFlow] /
IF(
Cashflow[DaysSince]=0,
1,
POWER(
1+ AVERAGE(RATES[Rate]),
(Cashflow[DaysSince]/365))
)
)

Cashflow[DaysSince]= trunc([Event Date]) - trunc(minx(FILTER(cashflow,( [Asset] = earlier([Asset]) && [Cashflow] <0 )),[Event Date]))

Cashflow[StandardisedDate] = =Date(1901,1,1)+[DaysSince]

Have fun.

Advertisements

DISCOVER_COMMANDS DMV – Improve Drillthrough & Learn DAX Query

In the past I’ve demonstrated a few examples of using DAX Query in PowerPivot workbooks, this “create a Date Dim” for example.

The ability to produce tables rather than just pivots can be very useful; not just for ETL-like activities like the above mentioned Date Dim, but also for testing and development; and for producing pivot friendly flattened tables for consumption by other other data vsiualisation toolsets.

I’ve been asked a few times what’s the best way to learn DAX Query? Well, in most respects, it’s the same as the standard DAX used to build measures and calculated columns, so just keep practicing that measure building!

But, another interesting way, is to see the DAX auto-created by a tool that, I suspect, was the original use-case for tabular DAX i.e. PowerView.

PowerView comes with Excel 2013 (at least those versions that also pack the PowerPivot add-in) so you no longer require a SharePoint farm to try it out.

(One thing to note if you’re running a 64bit version of Excel 2013, you’ll need to source a 64 bit version of Sliverlight; the 32bit download that you’ll be prompted to install via your (32bit) browser will not work.)

Here’s a good introduction to  report building in PowerView by MSBI Academy.

To see the DAX generated by PowerView (and indeed the MDX generated by the workbook’s pivotables) use the DMV view DISCOVER_COMMANDS.

For Excel 2010 you would have used my trick to fire DMV views, but this no longer works in 2013. So, instead, you can use this trick to execute any DAX, MDX or DMV.

Note: you’ll need a true external connection (a workbook table will not enable the Edit DAX option). Also, the table’s connection points at the model, so be careful with the Refresh command (i.e it’ll cause the table to refresh). It’s a little bit more complex, it depends on the nature of the DAX; the engine uses a dependency graph it seems (which is good, shows this feature is fully integrated). If your DAX mentions a column on a table, then that table will be refreshed.

The Edit Dax action uses a…

Connections(“ModelConnection_<<whatever>>”).Refresh

…which is obviously only between workbook and model, so doesn’t cause this external refresh.

You may also notice that sometimes the sequence of columns returned is different from expected, I usually find re-issuing the DAX command fixes this.

Because of the awkwardness of the above hack(s) when I don’t need this dependency stuff I usually revert to my HAMMER micro-ETL utility to do any DAX, MDX or DMV queries via the HAMMER(“PPCONECTION”, <<my command>>, “ADO”) command set.

You could also write your own ADO code, latching on to the ActiveWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection connection object.

Not only can you see examples of DAX Query construction using the above methods, but you can also run/modify the extracted commands using the same.

Another very useful use of DISCOVER_COMMANDS is to pick-up the MDX DRILLTHROUGH command issued when a Show Details is applied to a pivottable cell.

You can then, for example, change the MAXROWS returned (something that disappeared in 2013, only other method is to open the workbook ZIP source and modify the Connection’s XML).

But even better, you can pick a different set of columns to return. This is so useful, as normally the default DRILLTHOUGH usually doesn’t bring back useful columns such as the row’s unique ID!

See here for a workbook containing examples of the above.

To use the HAMMER commands you’ll need to manually open the xll (make sure to pick the correct bitness).

To enable HAMMER’s full functionality (namely any command, such as HAMMERtoFit, that uses a background thread) you’ll need to close and reopen Excel. This is something new in Excel 2013, probably related to ensuring that an internet-delivered workbook doesn’t  use something like a background thread to do something nasty before it has been “trusted”. Well that’s my theory anyway, and I’m sticking to it. Anybody else seen similar?

UPDATE: Another method to have fun with DAX Query expressions is Dax Studio, which now supports Excel 2013! http://geekswithblogs.net/darrengosbell/archive/2013/05/04/new-release—dax-studio-1.2-with-excel-2013-support.aspx

DAX’s back – Create DAX Measures in Excel 2013 without PowerPivot Add-in

I mentioned in my last post that it was possible to create DAX measures (and indeed Calculated Columns, KPIs etc.) without using the PowerPivot add-in but instead using AMO to do so. The facility has always existed in HAMMER but only as a hidden option that I often used to iterate quickly through a set of measure variations (or load standard sets) during model development; but I wasn’t 100% convinced it was worth the effort to make it more production quality in order to add it to HAMMER’s public interface. But, with the extraordinary removal of PowerPivot add-in as an option for all retail versions and all O365 SME versions of Office 2013, I decided to revisit the code and make it available as a new HAMMER command “PPMAKEMEASURES”.

This zip file contains an example of an Excel 2013 workbook (drawing data from a Northwind oData public feed) with DAX measures created by HAMMER rather than via the PP add-in.

The HAMMER formula and associated “list-of-measures” table can be found within.To use the formula on this, or another workbook, you need to activate HAMMER.xll (or HAMMER-64.xll, if using Excel 64bit) by either opening like a normal workbook (will not be installed as a permanent add-in) or put it in your add-ins folder and register as per a normal XLL (you can also use VBA’s registerDll functionality for automatic only-this-instance registering).

The first time you apply this method of DAX measure creation to a workbook, you should save, close & re-open the workbook; otherwise, you run the risk of the created measures being overwritten if you subsequently create an implicit measure (by moving a field to the Values section of a pivot table). If saved, this will no longer be a problem. If you use this in Excel 2010 you’ll always need to save,close & reopen in order to see the new measures. (Also, in 2010 you’ll need to precede any PP commands with the PPCONNECTION command e.g. =HAMMER(“PPCONNECTION”,Table2[#All],”PPMAKEMEASURES”) UPDATE: It appears you also need the PPCONNECTION in 2013 although I don’t appear to need it in my O365 version!).

To get a list of DAX Measures within a workbook use the PPLISTMEASURES command (e.g. =HAMMERToRange(“PPCONNECTION”,”PPLISTMEASURES,”Sheet2!A1″) will paste a table to Sheet2 starting at cell A1). The table will be in the format required by the PPMAKEMEASURES command and is intended as a quick way of transferring measures from one workbook (even if 2010) to another. (Note: this will list both implicit and explicit measures, best avoid creating implicit measures using this method!)

The table is position based (i.e. heading values not important, just as long as there is a heading line); columns represent – Table Name, Measure Name, DAX, Format and Visible. If the 3rd column, i.e. the DAX expression, is blank, the measure will be deleted, otherwise, any existing measures will be deleted and replaced with the new definitions.

This has only been tested against  O365 versiond of Excel 2013, retail versions may be different. I needed to hard-code the location of the AMO & ADOMD dlls (unlike 2010 they’re no longer in GAC and are not on a PATH – well, likely they are, but only in the virtual file system now used by Office).

The location for the AMO library for 32bit O365 Home Premium is  “C:/Program Files/Microsoft Office 15/root/vfs/ProgramFilesCommonX86/Microsoft Shared/OFFICE15/DataModel/Microsoft.Excel.AMO.dll” let me know if your setup is different. (UPDATE: As Marco discovered, enterprise and most likely retail versions do not use VFS, I‘ll post a fix to this tomorrow). UPDATE: PATH issues with none O365 versions should now be fixed.

If you’re a SME or sole-trader and you intend to use PowerPivot extensively you really need to purchase (as I have) the O365 Professional Pro subscription for your “datasmiths”. The rest of your crew can use Retail or O365 offerings (as they can still “consume” DAX powered models). But if, for some reason, you can’t or won’t purchase ProPlus, or you just want to take advantage of the fast build-redesign-build cycle offered by PPMAKEMEASURES, then hopefully, this will be of use.

Oh, and Happy St. Patrick’s day!

Níl aon Analytics mar d’Analytics féin.

Nollaig Shona Daoibh

Nollaig Shona Daoibh

The title is a play on the well know Irish seanfhocal (saying): “Níl aon tinteán mar do thinteán féin.” – “There’s no hearth (tinteán) like your own hearth”.

Apt for this time of year when families come together to celebrate Christmas.

Analytics ( aka Business Intelligence – even though I much prefer the term Decision Support above either term) is like politics and family, local and highly context sensitive. If it wasn’t, every ERP/CRM or packaged software application seller would have negated the reason for it using their respective “reporting modules” . They haven’t, and they wouldn’t, because although you bend your business to fit around such “best practice” and/or “good enough”  solutions your business (like your family) is unique. The standard patterns are there but the reality on the ground is always, always, different.

So if this Christmas you want to give a gift to that special datasmith in your life, introduce them to PowerPivot by buying them this book.

This is the book we should have had 2 or 3 years ago, it would have saved all of us early adopters a lot of pain and experimentation. Up until this book, anytime a “civilian datamsmith” would ask for a recommendation on a resource to help get started with DAX and PowerPivot I’d been at a loss (never really a problem when BI professionals asked the same question, as the Italians had that area well covered). But now, no hesitation,  if you want to master DAX within Excel, this is the book for you.

Nollaig Shona Daoibh, a Merry Christmas to you all.

Playing DAX Percentiles on the mean (or is that median) Streets of Ireland

A significant event in Ireland’s property market happened this week, there’s now a publicly accessible register of all residential property sales.

This is something that should have always been available but became absolutely essential as Ireland’s property boom unravelled, with prices down 60% since 2007. Not just, to help potential vendors and purchasers find a price point in a collapsed market, but also for all householders, now that a self-accessed property tax is due mid next year.

The database is intended as a search & compare tool rather than a property index and is missing at lot of the dimensionality that a proper index would have (such as no of beds, square footage etc.). As such, it’s not an ideal PowerPivot candidate – only 50000 odd records (due to low market activity – 1% turnover V. the 4/5% of a healthy market) and a large text “high-cardinality” “fact field” (or degenerate dimension if you like) i.e. the house address; limiting xVelocity’s ability to compress the dataset.

Also, “normal” SUM() and AVERAGE() statistic functions are not that useful in analysing such data; percentile and counts tend to be more informative. PowerPivot can handle the counts but lacks a percentile function!

Nevertheless, I decided to let PowerPivot loose on it.

I first added a small amount of dimensional decoration. I grouped the counties into regions such as Dublin, Greater Dublin (Kildare,Meath,Wicklow), Border, Midlands, South Leinster, West, & Munster.

I also added a date dimension, nearly always a good idea. To that I added a MAT (moving annual) attribute as, in general, I wished to look only at the last 12 months (more realistic prices). Before that pricing seems to be all over the place as the market collapsed. This method of updating MAT (or YTD etc.) attributes to suit the “current period” is an old DW trick that’s still useful even now with DAX’s time intelligence functionality (particularly when the base DAX formulas are tending on the complex side, as below).

Next, concoct a Percentile formula.

My first attempt involved using the TOPN function to separate out the portion of the table “above” the percentile, then use MIN() to find the value. Worked, was fast; but then I noticed it only worked where the “top” was less that 5000’ish elements if it was greater than that, the function returned the whole table! A bug, no doubt, and not the use-case envisaged for the function, I suppose 😦

I then went back to using a formula that I had used before based on Marco Russo’s median example. But when I applied it to the model I noticed it was very slow, using 99% of a CPU and using a burst of memory approximately 3 times that of the workbook at rest!

Okay back to the drawing board, this time I decided to use another new PP V2 function, RANK.EQ. This worked and is fast. See below :

percentilePrice:=
if(HASONEVALUE(Percentile_Selector[What_Precentile]),
	MINX(
		FILTER(propertyprices,
				RANK.EQ(propertyprices[Price €], propertyprices[Price €])
				<=
				COUNTROWS(propertyprices)*(1-Values(Percentile_Selector[What_Precentile]))+0.5
		),
		propertyprices[Price €])
,blank())

It’s a real pity that Percentile-like functions are missing from the current version of DAX as the above code, while not terribly complex, is not exactly easy either. And, while perfectly suitable for the type of dataset in this case, it won’t always match Excel’s in-built Percentile functions, for more on how to simulate Excel’s percentiles see http://www.powerpivotpro.com/2012/05/percentile-measures-in-dax-errata/ (but be aware these examples use the bug-prone TOPN).

Maybe the lesson here, is, sometimes good old plain Excel works better (and quicker) than PowerPivot, horses for courses.

Here’s the Irish Property Register in PowerPivot workbook …