DAX help needed

[Anonymous]

Hi everyone, 

I have a dax query that works very slow, and my data model consist of 1 fact table as well as 2 basic dimension like dim date, 

can you suggest an optimized code for the dax query below?

sales_ =
VAR delivery =
SUMMARIZE (
fact_sales,
fact_sales[id],
fact_sales[product],
"totalsales",
IF (
fact_sales[product_id] = "0",
CALCULATE (
SUM ( fact_sales[amount] ),
TOPN ( 4, fact_sales, fact_sales[s_id], DESC )
),
IF (
fact_sales[product_id] = "1",
CALCULATE (
SUM ( fact_sales[amount] ),
TOPN ( 2, fact_sales, fact_sales[s_id], DESC )
),
CALCULATE (
SUM ( fact_sales[amount] ),
TOPN ( 1, fact_sales, fact_sales[s_id], DESC )
)
)
)
)
RETURN
SUMX ( delivery, [totalsales] )

Thanks&Regards,

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

Hi @lbendlin ,

Thank you for your reply, I have shared sample data below and dax formula, really appreciate your help,

looking forward to hear your feedback.

id product product_id amount s_id 100-4024449860 ,3 3 1,9 10668506 100-4024449860 ,3 3 1,9 10668868 100-4024449860 ,3 3 1,9 10670033 100-4023063296 ,2 2 0 199334 100-4023063321 ,2 2 2,7 1866829 100-4023063351 ,2 2 5,8 78233 100-4023063537 ,2 2 0,8 199336 100-4023063906 ,2 2 21 1889161 100-4023063997 ,2 2 1,4 1984698 100-4023064113 ,2 2 5,5 1992505 100-4023064143 ,2 2 0 77493 100-4023064155 ,2 2 10,9 10037981 100-4023064180 ,2 2 18,4 1994162 100-4023064180 ,2 2 2,7 2015940 100-4023064180 ,2 2 3,2 2034646 100-4023064287 ,2 2 1,3 10613124 100-4023064570 ,2 2 1,7 182786 100-4023064570 ,2 2 1,7 183063 100-4023064735 ,2 2 5,5 1994150 100-4023064946 ,2 2 4,6 10289201 100-4023065082 ,2 2 4,9 10038215 100-4023065082 ,2 2 1,7 10038252 100-4023065116 ,2 2 0,4 10037979 100-4023065148 ,2 2 0,7 199364 100-4023065287 ,2 2 0,6 199356 100-4023065356 ,2 2 2,2 10607848 100-4023065360 ,2 2 1,2 183745 100-4023065604 ,2 2 0,6 10036460 100-4023065850 ,2 2 0,6 34275

sales_ =
VAR delivery =
SUMMARIZE (
fact_sales,
fact_sales[id],
fact_sales[product],
"totalsales",
IF (
fact_sales[product_id] = "0",
CALCULATE (
SUM ( fact_sales[amount] ),
TOPN ( 4, fact_sales, fact_sales[s_id], DESC )
),
IF (
fact_sales[product_id] = "1",
CALCULATE (
SUM ( fact_sales[amount] ),
TOPN ( 2, fact_sales, fact_sales[s_id], DESC )
),
CALCULATE (
SUM ( fact_sales[amount] ),
TOPN ( 1, fact_sales, fact_sales[s_id], DESC )
)
)
)
)
RETURN
SUMX ( delivery, [totalsales] )

[lbendlin]

can you please re-post the sample data in usable format?

[Anonymous]

id	product	product_id	amount	s_id
100-4024449860	3	0	1,9	10668506
100-4024449860	3	0	1,9	10668868
100-4024449860	3	0	1,9	10670033
100-4023063296	2	0	0	199334
100-4023063321	2	0	2,7	1866829
100-4023063351	2	0	5,8	78233
100-4023063537	2	0	0,8	199336
100-4023063906	2	1	21	1889161
100-4023063997	2	1	1,4	1984698
100-4023064113	2	1	5,5	1992505
100-4023064143	2	1	0	77493
100-4023064155	2	1	10,9	10037981
100-4023064180	2	1	18,4	1994162
100-4023064180	2	1	2,7	2015940
100-4023064180	2	2	3,2	2034646
100-4023064287	2	2	1,3	10613124
100-4023064570	2	2	1,7	182786
100-4023064570	2	2	1,7	183063
100-4023064735	2	2	5,5	1994150
100-4023064946	2	2	4,6	10289201
100-4023065082	2	2	4,9	10038215
100-4023065082	2	2	1,7	10038252
100-4023065116	2	2	0,4	10037979
100-4023065148	2	2	0,7	199364
100-4023065287	2	2	0,6	199356
100-4023065356	2	2	2,2	10607848
100-4023065360	2	2	1,2	183745
100-4023065604	2	0	0,6	10036460
100-4023065850	2	1	0,6	34275

@lbendlin , can you pls try this data?

[lbendlin]

Your sample data only has Product IDs 2 and 3 but the DAX explicitly calls out IDs 0 and 1.  Please clarify.

How many rows are in your actual table?

This article is likely relevant for your issue:  https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

[Anonymous]

well I have got performance issue and I have checked that article as well, but when i change it to addcolumns it is returning different value, so I want to optimize the query.

[lbendlin]

Doesn't this make more sense?

[Anonymous]

I have tried but it didnt return same value, do you think why?

[Anonymous]

Hi @lbendlin , I have fixed the sample data, can you try with new one, and my main point in the dax query is I want to find total sales based on id,product and during calculation there is if condition and it should provide different topn strategy according to product_id.

[lbendlin]

I still don't understand the business logic.  What are you trying to achieve?

[Anonymous]

Hi @lbendlin , I have tried but it looks messy again can you open the excel file below ?

https://drive.google.com/file/d/1w7YQPyczBTFPcszM8je4aAefhzNiarri/view?usp=drive_link

[Wilson_]

Hello sc6161,

Can you please provide a sample pbix file? (If you don't know how, please check out the pinned thread in the forum.)