# X functions in DAX?



## MrExcel (Jan 18, 2012)

DAX offers some functions that sound familiar, but they aren't. For example, SUMX, MINX, MAXX, COUNTX. I understand the difference between COUNT and COUNTA, but what is COUNTX?

Thanks

Bill


----------



## shg (Jan 18, 2012)

http://technet.microsoft.com/en-us/library/ee634549.aspx?


----------



## powerpivotpro (Jan 18, 2012)

The "X" functions are iterators, kinda like array formulas or a FOR...  NEXT loop.  SUMX, MINX, and MAXX are particularly helpful.  Imagine a calculated field in a pivot that returns the max sales for any single day in the month, when you don't even have the Day field on the pivot.

I also call these functions the "5-point palm, exploding function technique."  Quite simply the deadliest blow in all of the spreadsheet arts.  Example here:

http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/

Also, big thumbs up to Mr. Excel for launching this forum


----------



## David Churchward (Jan 18, 2012)

I think of it as a means to calculate a total that is a function of a formula executed at a lower level than your report displays and then aggregated up through the required reporting levels.

A good example is exchange rate conversion - USD / ER = GBP.  You need to execute this at every detail line and then sum the answer to get the sum of GBP as ER could be different for every single line.

It takes some head scratching in some scenarios, but it's an awesome set of functions.


----------



## powerpivotpro (Jan 18, 2012)

Well said David


----------



## AlbertoFerrari (Jan 19, 2012)

MrExcel said:


> DAX offers some functions that sound familiar, but they aren't. For example, SUMX, MINX, MAXX, COUNTX. I understand the difference between COUNT and COUNTA, but what is COUNTX?
> 
> Thanks
> 
> Bill


 
I suspect Bill is not interested in X functions as a generic topic but in the specific usage of COUNTX, which, I agree, is not very intuitive or common. 

Suppose you have a measure and want to count the number of rows in a table for which that measure is not blank. You can use:
COUNTX (MyTable, [MyMeasure])
While, obviously, you cannot use COUNT here, you definitely need an iterator (you might express the same using COUNTROWS (FILTER(...))).
Using it with measures makes some sense, using it with expressions inside the same table is something I have never done but, in some scenarios, it might save you the need to store calculated columns, which might be expensive on huge tables.
Alberto


----------



## Dan80 (Apr 29, 2016)

The problem I have found with the 'X' functions is that it saps the speed
If the pivot when reporting so I have had to rely on calculated columns instead and then summed up the values. Does anyone else experience this issue? Anyway around it? Thanks. 
Dan


----------

