Forum Discussion
Optimizing Measures to avoid Out of memory errors
hi Anonymous, I didn't have time to look into it earlier
can you have a look at this measure, I believe it gives the correct values e.g. 0.12OZ has 1 and 0.15OZ has null as it's was sold in 2004 & 2005)
UniquesCount =
VAR _Chars =
VALUES ( fData[Char Value] )
VAR _CharsAllYears =
CALCULATETABLE ( fData, _Chars, ALL ( dDate[Year] ) )
VAR _CharsSummary =
GROUPBY (
_CharsAllYears,
fData[Char Value],
"MaxYear", MAXX ( CURRENTGROUP (), RELATED ( dDate[Year] ) ),
"MinYear", MINX ( CURRENTGROUP (), RELATED ( dDate[Year] ) )
)
VAR _SameYearChars =
FILTER ( _CharsSummary, [MaxYear] = [MinYear] )
RETURN
COUNTROWS ( _SameYearChars )
Hi Stachu,
Thanks for your quick response.
This measure is pretty fast!
A few observations:
- It works well when i apply Year from dDate table and Char Value from fData table to Rows section of Pivot. I check using Conditional Formatting on the Char Values. However, if i add Country, Category or any other columns to the Columns section of pivot, i do not get unique Char Values in Rows anymore. I get a yellow message in Pivot Filter section saying "Relationships need to be created". This i think is because of Many-to-Many relationship in the fData table (see my previous posts or messages in your Inbox for the columns). I tried solving this by creating the addtional single-column tables for relationship with fData table e.g. dBrandOwner, dDate, dCharDescription, dCountry, dCategory etc which contain distinct values collected from fData only. But i think i does not solve the issue. How do i resolve such M2M issues?
- Will any other measures i create work properly, if i use this measure in the other measures? e.g. =CALCULATE ( [UniquesCount], filter, filter, filter....)
- Can you please also answer my questions from my 1st post & 2nd post?
- Can you please briefly explain how this measure you have created works?
Best,
Sifar
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 tablesHi 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
- e.g. there exists M2M between :
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.
OK, so I guess something like this should work, as long as you add the additional criteria you want to ignore to the
_CharsAllUniquesCount:=
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])
- 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.