why are matrix so slow (spoiler alert : because of the DAX)

[fsim]

Hi !

We have this report runing on a multi dimensional cube. I'm showing some data using on the left, a simple table and on the right a matrix. customer, number of shipment lines, year. not a big deal. 

It takes about 500ms for the table to show up and 3 minutes for the matrix.

So I had a look at the request  using daxstudio. and here are the queries

// =================
// Operation       : 2 
// Visual          : Table 
// Query Start     : 17/12/2020 10:17:43
// Query End       : 17/12/2020 10:17:43
// Render Start    : 17/12/2020 10:17:43 
// Render End      : 17/12/2020 10:17:43
// Query Duration  : 114 ms
// Render Duration : 136 ms
// Total Duration  : 250 ms
// Row Count       : 1
// =================
EVALUATE
  CALCULATETABLE(
    CALCULATETABLE(
      ROW(
        "No_of_Shipment_Receipt_Lines", 'Advanced Inventory'[No of Shipment Receipt Lines],
        "No_of_Shipment_Receipt_Lines_FormatString", 'Advanced Inventory'[No of Shipment Receipt Lines.FormatString]
      ), + the filters
  

and 

// =================
// Operation       : 3 
// Visual          : Matrix 
// Query Start     : 17/12/2020 10:17:43
// Query End       : 17/12/2020 10:20:16
// Render Start    : 17/12/2020 10:20:16 
// Render End      : 17/12/2020 10:20:16
// Query Duration  : 153035 ms
// Render Duration : 169 ms
// Total Duration  : 153204 ms
// Row Count       : 3
// =================
EVALUATE
  CALCULATETABLE(
    TOPN(
      102,
      SUMMARIZE(
        KEEPFILTERS(
          GENERATE(
            KEEPFILTERS(
              FILTER(
                KEEPFILTERS(VALUES('Customer Vendor'[Customer Only])),
                CALCULATE(
                  'Advanced Inventory'[No of Shipment Receipt Lines] > 0,
                  KEEPFILTERS(
                    FILTER(
                      KEEPFILTERS(VALUES('Transaction Date'[Calendar Year.Key0])),
                      NOT(ISBLANK('Advanced Inventory'[No of Shipment Receipt Lines]))
                    )                  )                )              )            ),
            CALCULATETABLE(
              FILTER(
                KEEPFILTERS(
                  SUMMARIZE(
                    VALUES('Transaction Date'),
                    'Transaction Date'[Calendar Year.Key0],
                    'Transaction Date'[Calendar Year]
                  )                ),
                NOT(ISBLANK('Advanced Inventory'[No of Shipment Receipt Lines]))
              )            )          )        ),
        ROLLUP(
          ROLLUPGROUP('Transaction Date'[Calendar Year.Key0], 'Transaction Date'[Calendar Year])
        ),
        "Calendar_Year_Key0IsAggregate", ISSUBTOTAL('Transaction Date'[Calendar Year.Key0]),
        "No_of_Shipment_Receipt_Lines", 'Advanced Inventory'[No of Shipment Receipt Lines],
        "No_of_Shipment_Receipt_Lines_FormatString", 'Advanced Inventory'[No of Shipment Receipt Lines.FormatString]
      ),
      [Calendar_Year_Key0IsAggregate],      1,
      'Transaction Date'[Calendar Year.Key0],      1,
      'Transaction Date'[Calendar Year],      1
    ), + the filters
  

how Can I speed up things ? Is this topn 102 avoidable ? Is this because of the multidimensional cube as a source ?

[fsim]

Hi ! thanks for your answers. 

the only measure in here is a count of the shipment lines. Nothing very fancy and, these are the same values shown in both table and matrix. I think there are about 1.3milions rows in the DB, that's why I'm pointing the topN 

😕

[daxer-almighty]

Nobody in their right senses would want or even like to analyze code like this :))) But it's automatically generated. As @AntrikshSharma said, the problem is your measures, not the query itself.

[AntrikshSharma]

@fsim This is just the code that PBI is creating behind the scene.. the real problem is in the measures that you have written, can you please include the code of those as well.

TOPN is used by PBI for optimization so that you don't see every result in the matrix.