cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jesly_ajin
Helper II
Helper II

Improve the performance of Measure Formula

I will need your support to improve performance for the below measure formula. This is taking too much time to execute.

 

ActualEqHours = CALCULATE (
SUMX ( SelfService_Plan_DailyPlanHour, IF(SelfService_Plan_DailyPlanHour[DailyPlanStatusId]=1,0,
IF(SelfService_Plan_DailyPlanHour[DailyPlanStatusId]=2,SelfService_Plan_DailyPlanHour[ActualStdHours],
if(SelfService_Plan_DailyPlanHour[DailyPlanStatusId]=3,SelfService_Plan_DailyPlanHour[ApprovedStdHours],SelfService_Plan_DailyPlanHour[ApprovedStdHours])))),
FILTER ( SelfService_Plan_DailyPlanHour,
SelfService_Plan_DailyPlanHour[IsActive_Hour]
&& SelfService_Plan_DailyPlanHour[IsLatest]
&& SelfService_Plan_DailyPlanHour[ResourceTypeDescription]="Equipment"
&& (SelfService_Plan_DailyPlanHour[DailyPlanStatusDescription]="Approved" || SelfService_Plan_DailyPlanHour[DailyPlanStatusDescription]="Awaiting Approval")
&& RELATED(SelfService_Control_CostItem[Is_terminal])
&& SelfService_Plan_DailyPlanHour[ProjectId]=RELATED(SelfService_Core_Project[Project_id])
),USERELATIONSHIP(SelfService_Plan_DailyPlanHour[ProjectIdEquipDisplay], InEightProjEquipmentStagingTable[ProjectIdEquipmentDisplay]))

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @jesly_ajin ,

Variables can be defined, I modified Dax like this, you can add it to your PBIX file to view the time spent:

ActualEqHours =
var _1=
SWITCH(
    TRUE(),
SelfService_Plan_DailyPlanHour[DailyPlanStatusId]=1,0,
SelfService_Plan_DailyPlanHour[DailyPlanStatusId]=2,SelfService_Plan_DailyPlanHour[ActualStdHours],
SelfService_Plan_DailyPlanHour[DailyPlanStatusId]=3,SelfService_Plan_DailyPlanHour[ApprovedStdHours],
SelfService_Plan_DailyPlanHour[ApprovedStdHours])

var _2=SUMX(SelfService_Plan_DailyPlanHour,_1)

return
CALCULATE ( _2,
FILTER ( SelfService_Plan_DailyPlanHour,
SelfService_Plan_DailyPlanHour[IsActive_Hour]
&& SelfService_Plan_DailyPlanHour[IsLatest]
&& SelfService_Plan_DailyPlanHour[ResourceTypeDescription]="Equipment"
&& (SelfService_Plan_DailyPlanHour[DailyPlanStatusDescription]="Approved" || SelfService_Plan_DailyPlanHour[DailyPlanStatusDescription]="Awaiting Approval")
&& RELATED(SelfService_Control_CostItem[Is_terminal])
&& SelfService_Plan_DailyPlanHour[ProjectId]=RELATED(SelfService_Core_Project[Project_id])
),USERELATIONSHIP(SelfService_Plan_DailyPlanHour[ProjectIdEquipDisplay], InEightProjEquipmentStagingTable[ProjectIdEquipmentDisplay]))

Here are the related Dax function optimization documents, I hope it will be helpful to you:

https://maqsoftware.com/expertise/powerbi/dax-best-practices

https://community.powerbi.com/t5/Desktop/DAX-optimization/td-p/946984

https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @jesly_ajin ,

Variables can be defined, I modified Dax like this, you can add it to your PBIX file to view the time spent:

ActualEqHours =
var _1=
SWITCH(
    TRUE(),
SelfService_Plan_DailyPlanHour[DailyPlanStatusId]=1,0,
SelfService_Plan_DailyPlanHour[DailyPlanStatusId]=2,SelfService_Plan_DailyPlanHour[ActualStdHours],
SelfService_Plan_DailyPlanHour[DailyPlanStatusId]=3,SelfService_Plan_DailyPlanHour[ApprovedStdHours],
SelfService_Plan_DailyPlanHour[ApprovedStdHours])

var _2=SUMX(SelfService_Plan_DailyPlanHour,_1)

return
CALCULATE ( _2,
FILTER ( SelfService_Plan_DailyPlanHour,
SelfService_Plan_DailyPlanHour[IsActive_Hour]
&& SelfService_Plan_DailyPlanHour[IsLatest]
&& SelfService_Plan_DailyPlanHour[ResourceTypeDescription]="Equipment"
&& (SelfService_Plan_DailyPlanHour[DailyPlanStatusDescription]="Approved" || SelfService_Plan_DailyPlanHour[DailyPlanStatusDescription]="Awaiting Approval")
&& RELATED(SelfService_Control_CostItem[Is_terminal])
&& SelfService_Plan_DailyPlanHour[ProjectId]=RELATED(SelfService_Core_Project[Project_id])
),USERELATIONSHIP(SelfService_Plan_DailyPlanHour[ProjectIdEquipDisplay], InEightProjEquipmentStagingTable[ProjectIdEquipmentDisplay]))

Here are the related Dax function optimization documents, I hope it will be helpful to you:

https://maqsoftware.com/expertise/powerbi/dax-best-practices

https://community.powerbi.com/t5/Desktop/DAX-optimization/td-p/946984

https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!