Forum Discussion
Optimizing Measures to avoid Out of memory errors
my guess the measure are not performaing beacause of the number of conditionals and iterator function you use
what do you want to measure in business terms with e.g. UniquesCount and UniqueCharsCount? why use IF and 1 as alternative resource?
can you also share anonymised sample of your data in copyable format here?
Hi Stachu,
Thanks for your quick response.
UniquesCount : is the count of unique Char Values that occurred first time within a year and not found in subsequent years. They can occur more than once within the year. I need to know their Firstdate of appearance (Item Appearance Date).
UniqueCharsCount : is the same as above. The only thing is i am counting their more than once occurence within the year as 1.
I need to know when UNIQUE Char Values appeared First time i.e FirstDate
- for a BrandOwner (Manufacturer)
- in a Country
- in a Category
I also need to know when DISTINCT Char Values appeared First time i.e FirstDate for :
- other Brand Owners (Manufacturers) and when (FirstDate)
- in other Countries and when (FirstDate)
- in other Categories and when (FirstDate)
i.e. how their paths have traversed across different Countries, Categories, Manufacturers over the years.
Also, is there a way to reduce file size for sharing purposes?
P.S: How do i anonymize this large data stretching different countries, categories, brand owners and years? Is there some free tool to do this?
can you just share the Top20 rows from your data table and the expected output for the shared sample?
here is a post on how to share:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
regarding the measures:
does UniqueCount give you correct results? for the first date of appearance SUMMARIZE by Char with ALL and MIN on date should workHi Stachu,
I have shared the sample anonymized testdata workbook for your inspection that you can download. I have added the 2 tables to the Data Model + the measures and created a sample pivot with Slicers. You can add Unique conditional formatting to E column to know if Char Values are indeed unique across Categories, Countries, BrandOwners & Years.With the huge amount of data, i am not sure i am getting Uniques correctly. This is because a Char Value may appear in multiple Countries or Categories.
Regarding the FirstCharValDateofAppearance, i am not sure how to write this measure or represent it in pivot. Would need some help here.
Let me know if this is sufficient or you need anything else.