1

I hope I'm not missing an easy solution am still getting used to DAX and can't yet find an appropriate logic.

I have a large dataset, >10m rows which I want to test. An identifier column "DocumentNumber" might occur on multiple rows and I want to find where the sum of "Value" over these rows for a given "DocumentNumber" is non-zero.

  • Tried to use PowerQuery > removed all but these two columns > Group By > DocumentNumber > Sum of Value. However my 32 bit version of Excel appears to run out of memory performing this step Expression.Error: Evaluation ran out of memory and can't continue.

  • Wrote a DAX measure > Sum of Values and dropped into a pivot table with a view to filtering out the zero values but when I try to drag in the DocumentNumber to rows there are more than a million rows so the table won't render.

Is there a logic I should follow in DAX that would achieve step 2 before bringing it to the pivot table? Can DAX actually create a new table in the data model which is the aggregated and filtered data rather than using a pivot? I believe this is possible in PowerBI but not sure about Excel evironment.

Alex Sergeenko
  • 642
  • 5
  • 22
Hugh Barry
  • 11
  • 2
  • 1
    Have you tried using [Summarize](https://msdn.microsoft.com/en-us/query-bi/dax/summarize-function-dax)? – Rory Aug 09 '18 at 14:57
  • Hi Rory, i wasn't aware of it but just tried it out and must have my syntax wrong. I created a two column table `DataTable` with columns `Type` and `Quantity` Tried to follow syntax on msdn as follows: '=SUMMARIZE('DataTable' ,'DataTable'[Type] ,"Summarised Quantity", SUM('DataTable'[Quantity]) )` but throws an error that it is referring to mulitple columns which cannot be converted to scalar – Hugh Barry Aug 09 '18 at 15:29
  • 1
    That's probably because you've created a table but you're trying to store it as a measure. Did you click create 'Calculated Table'? – Rory Aug 09 '18 at 15:35
  • @Rory thanks for that hint, I wasn't at all familiar with how to work with measure-created tables. Have since done some research and found how to create a table in an XL worksheet, edit the DAX to'EVALUATE – Hugh Barry Aug 15 '18 at 13:09
  • @Rory thanks for that hint, wasn't familiar with how to work with measure-created tables. Since done some research and found how to create a table in an XL worksheet, edit the DAX to EVALUATE("SUMMARIZE MEASURE") and this has returned a table of summarized valued by document, great! However this could be a long list as above and not necessarily fit a worksheet table if using a full dataset. I tried to EVALUATE(FILTER("SUMMARIZE MEASURE","SUMMARIZE MEASURE"[AggregatedColumnName]) again got a DAX error that the expression referred to multiple columns. Any ideas what's up with filter step? – Hugh Barry Aug 15 '18 at 13:15

0 Answers0