Need Opinion - Optimize Matrix Visualization with Multiple Measures

[stochasticKL]

Hi,

I'm trying to create a matrix visual for different company KRAs. I have five different result areas (RA) in different tables. Each RA table looks like this:

Result Area	Hierarchy 1	Hierarch 2	Date	Value
RA_1	H1_A	H2_A	1/1/2020	1
RA_1	H1_A	H2_B	1/1/2020	1
RA_1	H1_A	H2_A	12/1/2019	2

My goal is to visualize it to a single matrix with Result Area, Hierarchy 1, Hierarchy 2 as rows and Current Month, Previous Month, Previous Year, and Corresponding %Change as columns. Note that each RA is computed differently (e.g. RA_1 is aggregated as a SUM, RA_2 as an AVERAGE, RA_3 as LAST VALUE). Moreover, I have to use measures for the columns so that it dynamically computes values depending on the selected month and year. What I've done so far: 

1. In power query, I appended these RA tables into a single Main table (around 5,000 rows by 5 columns).
2. To create a measure for the Current Month, I have to create a measure which switches between formulas depending on which RA row it is SWITCH ( MAX ( Result Area ) , "RA_1" , *SUM* , "RA_2" , *AVERAGE* )  .
3. Previous months where then calculated from the Current Month using parallel period. 

At first, this seems to run smoothly. But later on, when I add more and more previous months column (say 12 columns for the past 12 months), it takes around 20 secs for the whole matrix to load and its taking up all 8 GB memory.

How can I tackle this situation more efficiently? 

Thank you! 

[v-juanli-msft]

Hi @stochasticKL 

First, please use View->Performance Analyzer, find what steps cause low performance.

Share the query here, we could discuss how to modify the query to get a more quick performance.

When showing last n months data on the matrix, could you accept showing as below:

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.