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,

 

Can anybody from the PowerBI community go through the comments and help quickly?

 

Here is the sample testdata.xlsx file.

 

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    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 )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Stachu,

       

      Thanks for your quick response.

       

      This measure is pretty fast!

       

      A few observations:

      1. 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?
      2. Will any other measures i create work properly, if i use this measure in the other measures? e.g. =CALCULATE ( [UniquesCount], filter, filter, filter....)
      3. Can you please also answer my questions from my 1st post & 2nd post?
      4. Can you please briefly explain how this measure you have created works?

       

      Best,

       

      Sifar

      • Stachu's avatar
        Stachu
        Community Champion
        7 years ago

        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