topic Re: Issue With Table Performance in DAX Commands and Tips

Issue With Table Performance

eliasayyy — Fri, 21 Jul 2023 07:56:23 GMT

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 measure for price is

new Price = MAX('Price Change'[Accumulated]) * MAX(Items[Item Price])

and the one used in the table

Final Price = IF( [new Price] = BLANK() , CALCULATE(MAX(Items[Item Price])) , [new Price])

here is my data model

so why is it affecting my perfromance?

if it helps here i price change table

and the powerquery Code

let Source = Excel.Workbook(File.Contents("C:\Users\WorK\Desktop\Sales Report.xlsx"), null, true), #"Price Change_Sheet" = Source{[Item="Price Change",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Price Change_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Items ID", type text}, {"Price Change", Percentage.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Items ID"}, {{"Table", each _, type table [Date=nullable date, Items ID=nullable text, Price Change=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let NewTable = [Table], Merged = Table.NestedJoin(NewTable, {"Date"}, Calendar, {"Date"}, "Calendar", JoinKind.FullOuter), ExpandedTable = Table.ExpandTableColumn(Merged, "Calendar", {"Date"}, {"Calendar Date"}), FilledDownTable = Table.FillDown(ExpandedTable, {"Items ID"}), FilledTable = Table.FillUp(FilledDownTable, {"Items ID"}) in FilledTable), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Price Change", "Calendar Date"}, {"Price Change", "Calendar Date"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Calendar Date", type date}, {"Price Change", Percentage.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Table"}), #"Sorted Rows1" = Table.Sort(#"Removed Columns",{{"Calendar Date", Order.Ascending}}), #"Grouped Rows1" = Table.Group(#"Sorted Rows1", {"Items ID"}, {{"Count", each _, type table [Items ID=nullable text, Price Change=nullable number, Calendar Date=nullable date]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each let NewTable = [Count], Accumulated = List.Skip(List.Accumulate( NewTable[Price Change], {1}, (st, cur) => st & {List.Last(st) * (1 + (if cur = null then 0 else cur))} )), CombinedTable = Table.FromColumns({NewTable[Items ID], NewTable[Calendar Date], Accumulated}, {"Items ID", "Calendar Date", "Accumulated"}) in CombinedTable), #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Calendar Date", "Accumulated"}, {"Calendar Date", "Accumulated"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom1",{"Count"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Calendar Date", type date}, {"Accumulated", type number}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Calendar Date", "Date"}}) in #"Renamed Columns"

and the attached dataset Sales Report.xlsx

Re: Issue With Table Performance

OwenAuger — Sat, 22 Jul 2023 04:51:38 GMT

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,

Re: Issue With Table Performance

eliasayyy — Sat, 22 Jul 2023 14:39:56 GMT

yes of course heres my pbix file Sales Presentation.pbix
and dataset Sales Report.xlsx

Re: Issue With Table Performance

OwenAuger — Sun, 23 Jul 2023 05:44:26 GMT

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

Re: Issue With Table Performance

eliasayyy — Sun, 23 Jul 2023 09:10:31 GMT

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