DAX Memory Issue, Text Comparison Calculated Column

[rlansing]

Hello,

I am looking at weekly sales data of various products and attempting to use an unrelated parameter table to sort the items into various groups.

So I have a Products table with Sub-Category, Product Style, Type, Brand, UPC, etc... and a segment table with the same columns.

I am trying to create a calculated column in the Products table that determines if the columns have the same values between the Products and Segment tables, then select the value from the "Custom Segment" column in the Segment table. Here is my calculation:

Custom Segment = CALCULATE( VALUES( Segmentation[Cust Segment] ), FILTER( Segmentation, Products[Sub-Cat] = Segmentation[Sub-Cat] &&
					( Segmentation[Brand] = "" || Products[Brand] = Segmentation[Brand] ) && ...

This works with about 3 or 4 columns, but it quickly runs out of memory or locks up the program if I push it to compare 5 or more columns.

Is there a way around this? Any ideas on a better memory friendly calculation?

Thank you for any help!

Bobby

[rlansing]

Here is the full calculation that is out of memory:

Custom Segment = CALCULATE( VALUES(Segmentation[Cust Segment]), FILTER( Segmentation, Products[Sub-Cat] = Segmentation[Sub-Cat] &&
					( Segmentation[Prod Module] = "" || Products[Prod Module] = Segmentation[Prod Module] ) &&
					( Segmentation[Prod Type] = "" || Products[Prod Type] = Segmentation[Prod Type] ) &&
					( Segmentation[Style] = "" || Products[Style] = Segmentation[Style] ) &&
					( Segmentation[Brand] = "" || Products[Brand] = Segmentation[Brand] )))

[BhaveshPatel]

Hi You can use DAX Studio to see what is causing this problem.

I would suggest you to use direct filters in the calculate statement and It will definitely give you better results. 

However, You should consider the amount of RAM installed & database size when performing such calculations with the iterator functions like FILTER. 

Thanks & Regards,

Bhavesh

[rlansing]

I tried the Dax Studio, but it gave a lot of errors. Not sure what was wrong.

What do you mean by direct filters?

I just stumbled upon this note on a DAX website (http://www.daxpatterns.com/parameter-table/😞

IMPORTANT
The RTM version of Analysis Services 2012 has an issue in SWITCH implementation, which internally generates a series of nested IF calls. Because of a performance issue, if there are too many nested IF statements (or too many values to check in a SWITCH statement), there could be a slow response and abnormal memory consumption. Service Pack 1 of SQL Server 2012 and PowerPivot 2012 fixed this issue. This is not an issue in Excel 2013.

This is almost exactly my issue. Any thoughts?