TLDR: I want to pre calculate matrix outputs dictated by slicers in the report so they are effectively cached with the dataset, we update our dwh every morning so once the data is there the values won't need to change but the matrix visual can use slicers to display different granularity configurations.
I've been given a requirement to calculate a matrix of KPIs which can use slicers to adjust the granularity of the output. Each of these columns are then conditionally formatted against an average for the given granularity.
I.e YTD at a Region Level where each site is formatted against the average for the given region, or Last 12 Months at a Territory level where the Territory
The logic works fine until I publish it to the service which then generates a memory error and upon digging have seen I'm exceeding the 1gb memory on visual calculation.
I have been working on this for about 2 months and have tuned my DAX queries but have failed to get it working within the memory requirements. This made me wonder if there is a way to calculate the 30 or so different combinations of this matrix as part of the refresh and then just display the desired version when the user accesses the report rather than trying to calculate it.
Google seems to push me towards calculated columns/tables but as I understand it, due to the order of execution these can't be impacted by slicers so is the same as passing a table to the dataset and using measures to return the appropriate values. Though I'm new to Power BI so may have misunderstood this.
I'm really grateful for any ideas or suggestions as have been struggling with this for over 2 months.
