Forum Discussion

TimmK's avatar
TimmK
Helper IV
4 years ago

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?

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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_))