Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, i'm having trouble with a slow DAX formula. The first formula takes 1 second to run, but when adding a "HASONEVALUE(productions[Productionid]) to determine if i'm calculating for one production order for multiple (the sum at the bottom of the table) it takes around 60 seconds.
The desired table shows the remaining hours for a given production order, and also shows the number, if it's a negative number. However the total should not include the number, if it's negative. ( see example)
Query 1 (1-2 seconds)
Remaining Hours:=
/*Create a smaller table, before i do calculations*/
Var T1 = CALCULATETABLE(
SUMMARIZE(Productions, /*Table*/
Productions[ProductionId],Productions[ProductionStatus] /*Group by*/
)
,Productions[ProductionStatus]<>"Ended" && Productions[ProductionStatus] <> "Finished") /*Filters*/)
Return
SUMX(T1,
Var Result =CALCULATE([BudgetHours] - [Actual production hours])
Return
IF(Result<0,0,Result)
)
Query2 (60 seconds)
Remaining Hours test:=
/*Create a smaller table, before i do calculations*/
Var T1 = CALCULATETABLE(
SUMMARIZE(Productions, /*Table*/
Productions[ProductionId],Productions[ProductionStatus] /*Group by*/
)
,Productions[ProductionStatus]<>"Ended" && Productions[ProductionStatus] <> "Finished") /*Filters*/
Return
IF(HASONEVALUE(Productions[ProductionId]),
/*If true (single line)*/
CALCULATE([Forkalkulerede timer by created Date] - [Actual production hours],Productions[ProductionStatus] <> "ended" && Productions[ProductionStatus]<>"Finished"),
SUMX(T1,
Var Result =CALCULATE([BudgetHours] - [Actual production hours])
Return
IF(Result<0,0,Result)
) )
Hi @Anonymous
You might consider providing your dummy pbix that would be helpful for us to investigate it further.
You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.
@Anonymous , what ar formula for [BudgetHours] , [Actual production hours].
What are you trying to do with hasonevalue
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi @amitchandak,
The HASONEVALUE is to return 2 different results.
If there is only 1 production i.e. we are inside the table, it should return [BudgetHours] - [Actual Hours] (this will either return a postive or negative number)
But if there are 2+ i.e. we are in the "sum" part of the table. it should return a SumX, where [BudgetHours] - [Actual Hours] is returned for each row. However rows with a negative number will be set to 0.
Nothing fancy in [BudgetHours] and [Actual production hours].
Actual production hours:= SUM(ProcessingTransactions[Hours])
BudgetHours:=SUM(Routes[BudgetHours])
Output looks like this. BudgetHours column is all to the right. The sum of the column is around 3500, but since i'm couting negative columns as 0, the value returns as 4195,65. This is due to the HASONEVALUE.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |