Forum Discussion

TimmK's avatar
TimmK
Helper IV
4 years ago
Solved

Slow Performance

I follow the star schema having the DIM tables "Item" and "Supplier", as well as the FACT table "Price".

 

These are the measures I use:

Measure 1 = 

CALCULATE(MAX('Price'[Start Date]),FILTER('Price','Price'[Start Date]<>MAX('Price'[Start Date])))

 

Measure 2 = 

VAR SL_ = [Measure 1]
RETURN
CALCULATE(SUM('Price'[Price]),'Price'[Start Date]=SL_)

 

With measure 1 I determine the second to last date. With measure 2 I use measure 1 to determine the price existing on the second to last date.

 

Generally it works, but the performance of the table is very slow.

 

In contrast, when I merge "Item" and "Supplier" with the "Price" table, thus having one large table with everything, the performance is fast. However, then I lose the benefits of the star schema.

 

Why is the performance to slow under the star schema and how can I make it fast?

 

The report table uses "Item" table (colums article number, article description), the "Supplier" table (column supplier name) and the "Price" table (columns unit, minimum quantity) as well as the mentioned measures.

  • TimmK , Try with these changes

     

    Measure 1 =

    CALCULATE(MAX('Price'[Start Date]),FILTER('Price','Price'[Start Date]< MAX('Price'[Start Date])))

     

    Measure 2 =
    VAR SL_ = [Measure 1]
    RETURN
    CALCULATE(SUM('Price'[Price]),filter('Price', 'Price'[Start Date]=SL_))

1 Reply

  • TimmK , Try with these changes

     

    Measure 1 =

    CALCULATE(MAX('Price'[Start Date]),FILTER('Price','Price'[Start Date]< MAX('Price'[Start Date])))

     

    Measure 2 =
    VAR SL_ = [Measure 1]
    RETURN
    CALCULATE(SUM('Price'[Price]),filter('Price', 'Price'[Start Date]=SL_))