cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Optimizing Measures to avoid Out of memory errors

I'm still not clear what you want to measure exactly - e.g. if there is [Char Value] in 1999 in France and in 2000 in DE should it be counted as unique or not?
regarding the error message - do you have relationships between dimensions tables? in order for them to work there should be 1 to many relationship, I don't really see why it would be many to many, and I don't think they're necessary in fData if you have them in dimension tables

sifar786 Member
Member

Re: Optimizing Measures to avoid Out of memory errors

Hi @Stachu,

 

  • I have updated the TestData Workbook with dimension tables (and a Metrics table to avoid clutter) that i have created from the Facts table i.e. fData table, so it acts as a Cross table, just to solve the M2M relationship issue.
    • e.g. there exists M2M between :
      • Category and Country
      • Country and BrandOwner
      • Category and BrandOwner
      • Char Description and Char Value

You can see it in the relationship window in PowerPivot. Am i doing it correctly?

 

  • If you see the pivots i have created, the 1st pivot shows the Unique Char values (names) per year, but the UniquesCount metric does not show how many times they appeared in that year. That is missing!

 

  • In the 2nd Pivot, if i put Country dimension in Columns section of pivot to see in which Country this Unique Char value originated, then the Char Values show Duplicates also (i have used Conditional Formatting to highlight Uniques). e.g. POWDER found in 2010, 2012.

 

  • if we get 2 Years of data, can we compare Past Year vs Previous Year to determine "Emerging" or "Trending" values?

 

  • How do i show the FirstDate (Item Appearance Date) when DISTINCT Char Values launched in a Country OR a Category to see a timeline Trend of how it travelled from one Country OR Category to another over the years?

 

  • Anything else interesting that might inform what additional insights we can find from this data.

 

Hope this makes it clear.

Super User
Super User

Re: Optimizing Measures to avoid Out of memory errors

OK, so I guess something like this should work, as long as you add the additional criteria you want to ignore to the 
_CharsAll

UniquesCount:=
VAR _Chars =
VALUES ( fData[Char Value] )
VAR _CharsAll =
CALCULATETABLE ( fData, _Chars, ALL ( dDate[Year] ), ALL(dCountry[Country]) ) --add other filters here
VAR _CharsSummary =
GROUPBY (
_CharsAll,
fData[Char Value],
"MaxYear", MAXX ( CURRENTGROUP (), RELATED ( dDate[Year] ) ),
"MinYear", MINX ( CURRENTGROUP (), RELATED ( dDate[Year] ) ),
"NrOfChars", COUNTAX(CURRENTGROUP(), fData[Char Value])
)
VAR _SameYearChars =
FILTER ( _CharsSummary, [MaxYear] = [MinYear] )
RETURN
SUMX(_SameYearChars,[NrOfChars])

 

 

 

sifar786 Member
Member

Re: Optimizing Measures to avoid Out of memory errors

Hi @Stachu,

 

I am getting the following memory error everytime (tried restarting excel also) when applying the measure on my original dataset, though i am using Excel 2016 Pro 64 bit, with 8 GB RAM on a Windows 10 64 bit machine.

 

mem error.JPG

 

UniquesCount:=

// get unique Char values
VAR _Chars = VALUES ( fData[Char Value] ) 

// Filter fData table based on only the unique Char values
VAR _CharsAll =
 CALCULATETABLE ( 
	fData, 
	_Chars, 
	ALL ( dDate[Year] ), 
	ALL ( dDate[Date] ),
	ALL ( dCountry[Country] ), 
	ALL ( dCategory[Category] ), 
	ALL ( dBrandOwner[BrandOwner] ), 
	ALL ( dBrand[Brand] ),
	ALL ( dSubBrand[SubBrand] ),
	ALL ( dInnType[InnType] ),
	ALL ( dInnSubType[InnSubType] ),
	ALL ( dCharDescription[CharDescription] )
)

// 
VAR _CharsSummary =
	GROUPBY ( 
		_CharsAll, 
		fData[Char Value],
		"MaxYear", MAXX ( CURRENTGROUP (), RELATED ( dDate[Year] ) ),
		"MinYear", MINX ( CURRENTGROUP (), RELATED ( dDate[Year] ) ),
		"NrOfChars", COUNTAX( CURRENTGROUP (), fData[Char Value] )
 )

//
VAR _SameYearChars = FILTER ( _CharsSummary, [MaxYear] = [MinYear] )

RETURN SUMX( _SameYearChars, [NrOfChars] )

I have added the additional filters. Can this measure be optimized?

Highlighted
sifar786 Member
Member

Re: Optimizing Measures to avoid Out of memory errors

Hi @Stachu ,

 

Any update on the issue?