Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Optimizing Measures to avoid Out of memory errors

I need help in understanding how to optimize some measures and have a few questions. My system configuration is :   OS : Windows 10 (64 bit) Memory : 8 GB RAM MSOffice : Office 2016 Pro (64 bit)...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.

Stachu's avatar
Stachu
Community Champion
7 years ago

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])