Very slow perfomance

[Analitika]

How to improve performance of table matrix?

PBI

https://easyupload.io/dvwdc0

[amitchandak]

@Analitika , not too bad in my case. The first matrix is slow. All values of the measure are the same. Is it expected or some measure problem

[Analitika]

It only 2 customer of 100.000  on original version not loading at all, problem with m_SSA_3 measure. I have no idea how to make it work

[edhans]

The problem is this M_SSA_3 measure:

m_SSA_3 =
VAR valText1 = [ABC_LIKUČIAI_1]
VAR valText12 = [ABC_LIKUČIAI_2]
VAR Result =
    CALCULATE(
        CALCULATE(
            CALCULATE(
                SUM( Table1[OPE_SUMOL] ),
                FILTER(
                    Table1,
                    Table1[OP_ATS]
                        IN {
                        0
                    }
                        && NOT Table1[OP_VIE] = 3
                        && NOT Table1[ZU0_KOD]
                        IN {
                        valText1,
                        valText12
                    }
                )
            ),
            FILTER(
                ALL( 'Date'[Date] ),
                'Date'[Date]
                    <= MAX( 'Date'[Date] )
            )
        )
            - CALCULATE(
                CALCULATE(
                    SUM( Table1[OPE_SUMOL] ),
                    FILTER(
                        Table1,
                        Table1[OP_ATS]
                            IN {
                            0
                        }
                            && Table1[OP_VIE] = 3
                            && NOT Table1[ZU0_KOD]
                            IN {
                            valText1,
                            valText12
                        }
                    )
                ),
                FILTER(
                    ALL( 'Date'[Date] ),
                    'Date'[Date]
                        <= MAX( 'Date'[Date] )
                )
            )
            - CALCULATE(
                CALCULATE(
                    SUM( Table1[OPE_SUMOL] ),
                    FILTER(
                        Table1,
                        Table1[OP_ATS]
                            IN {
                            2
                        }
                            && NOT Table1[ZU0_KOD]
                            IN {
                            valText1,
                            valText12
                        }
                    )
                ),
                FILTER(
                    ALL( 'Date'[Date] ),
                    'Date'[Date]
                        <= MAX( 'Date'[Date] )
                )
            )
            - CALCULATE(
                CALCULATE(
                    SUM( Table1[OPE_SUMOL] ),
                    FILTER(
                        Table1,
                        Table1[OP_ATS]
                            IN {
                            0
                        }
                            && Table1[ZU0_KOD]
                            IN {
                            valText1,
                            valText12
                        }
                            && Table1[_MZA2] > 0
                    )
                ),
                FILTER(
                    ALL( 'Date'[Date] ),
                    'Date'[Date]
                        <= MAX( 'Date'[Date] )
                )
            )
            - CALCULATE(
                CALCULATE(
                    SUM( Table1[OPE_SUMOL] ),
                    FILTER(
                        Table1,
                        Table1[OP_ATS]
                            IN {
                            0
                        }
                            && Table1[ZU0_KOD]
                            IN {
                            valText1,
                            valText12
                        }
                            && Table1[_MZA2] < 0
                    )
                ),
                FILTER(
                    ALL( 'Date'[Date] ),
                    'Date'[Date]
                        <= MAX( 'Date'[Date] )
                )
            ),
        FILTER(
            Table2,
            Table2[SK/AV] = "IAT"
        )
    )
RETURN
    /*IF (
       "PREK_G" IN DISTINCT ( Table1[rus_kod] )|| "NUOLAI" IN DISTINCT ( Table1[rus_kod] ), Result, -Result 
    )*/
    IF(
        SELECTEDVALUE( Table1[rus_kod] ) = "PREK_G"
            || SELECTEDVALUE( Table1[rus_kod] ) = "NUOLAI",
        Result,
        - Result
    )

And the M_SSA_4 is only slightly better. Those nested CALCULATES are doing all sorts of context transitions and reitterating over the tables. That needs to be totally reworked, and it needs to be scoped out as to what it is trying to do.

[Analitika]

I know that, but dont know how to improve that sh*t. 

Calculates different rows sums by different markers, as they describe different things such income, outcome, stocks and etc.

[edhans]

This is really more of a consulting question and you'd need to spend some time explaining to a DAX developer what the goal is and how the data is laid out. Perhaps someone can jump in, but this looks like several hours of work to me.

[Analitika]

I myself have not figured out the logic yet, I got such a gift from previous analysts