Forum Discussion

eliasayyy's avatar
eliasayyy
Memorable Member
3 years ago
Solved

Issue With Table Performance

I am building a sales table  when i add simple calculations i have a fast perfromance as soon as i added "Price" it became very slow and sometime it give an error   my m...
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    Hi again eliasayyy 

    Thanks for that 🙂
    I'm thinking an acceptable solution may just be to only display the [new Price] and [Final Price]  measures if Sales is nonempty.

     

    The underlying issue is that the measures are based on Item and Price Change tables that are not filtered by Customer, so will return a result for every Customer when Customer Name is included in the visual. We can restrict the combinations of Item/Customer by including only those combinations that occur in Sales, by checking if Sales is nonempty.

     

    new Price = 
IF (
    NOT ISEMPTY ( Sales ),
    MAX('Price Change'[Accumulated]) * MAX(Items[Item Price])
)

    Note: Used COALESCE as alternative to IF.

    Final Price = 
IF (
    NOT ISEMPTY (Sales ),
    VAR NewPrice = [new Price]
    RETURN
        COALESCE ( NewPrice, MAX ( Items[Item Price] ) )
)

     

    Does this or something similar work for you?

     

    Updated PBIX attached.

     

    Regards,

    Owen

OwenAuger's avatar
OwenAuger
Super User
3 years ago

Hi eliasayyy 

  • Would you be able to attach your PBIX file, or could you post a single data model diagram showing all tables together?
  • Which table contains the "Client Name" column used in the visual?

My suspicion is that the cause of the issue is that, in the cases where new Price is blank, Final Price returns a value that depends only on the Items dimension table. This could be a problem since, if any columns are included as "grouping columns" in the visual that do not filter the Items table (such as possibly "Client Name"), then all values of "Client Name" will be displayed for each Item Name (roughly speaking a crossjoin of values of those two columns will return nonblank results and be shown in the visual).

 

The solution could be to apply some logic to limit the Client Name & Item Name combinations shown, for example by just showing Item/Client combinations that exist in the Sales table. It would be easier to answer with the PBIX file or more detail on the data model.

 

Here's one idea you could try that limits Item ID values to just those existing in Sales, but it would be good to see the full model diagram and possibly test in a copy of the PBIX.

 

I also used ISBLANK rather than testing equality to BLANK ().

 

Final Price =
VAR NewPrice = [new Price]
RETURN
    IF (
        ISBLANK ( NewPrice ),
        CALCULATE (
            MAX ( Items[Item Price] ),
            -- only Items that exist in Sales based on other filters
            SUMMARIZE (
                Sales,
                Items[Item ID]
            )
        ),
        NewPrice
    )

 

 

Regards,

  • OwenAuger's avatar
    OwenAuger
    Super User
    3 years ago

    Hi again eliasayyy 

    Thanks for that 🙂
    I'm thinking an acceptable solution may just be to only display the [new Price] and [Final Price]  measures if Sales is nonempty.

     

    The underlying issue is that the measures are based on Item and Price Change tables that are not filtered by Customer, so will return a result for every Customer when Customer Name is included in the visual. We can restrict the combinations of Item/Customer by including only those combinations that occur in Sales, by checking if Sales is nonempty.

     

    new Price = 
IF (
    NOT ISEMPTY ( Sales ),
    MAX('Price Change'[Accumulated]) * MAX(Items[Item Price])
)

    Note: Used COALESCE as alternative to IF.

    Final Price = 
IF (
    NOT ISEMPTY (Sales ),
    VAR NewPrice = [new Price]
    RETURN
        COALESCE ( NewPrice, MAX ( Items[Item Price] ) )
)

     

    Does this or something similar work for you?

     

    Updated PBIX attached.

     

    Regards,

    Owen

    • eliasayyy's avatar
      eliasayyy
      Memorable Member
      3 years ago

      oh i get it now seems to make it faster thank you very much