Forum Discussion
DAX help needed
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,
12 Replies
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.)
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-Forum/ba-p/963216
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/1447523Hi 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] )can you please re-post the sample data in usable format?
