The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi Everyone
"The measure below is functioning correctly, and the values it produces are accurate. However, I am facing performance issues. To address this problem, I attempted to create another measure using the SUMMARIZE function. Unfortunately, the new measure does not provide correct values like the previous measure. I would appreciate your suggestions and ideas to help me resolve this issue."
---using calculate
Commercial Install =
Var _le = CALCULATE(SUM(ST_Activity_Report[PaidDuration]),ST_Activity_Report[BusinessUnit]
IN {"Commercial Install","Commercial - Installs","Commercial Services - Buffalo"},
USERELATIONSHIP(ST_Activity_Report[Indirect Key],ST_PFP_Report[ID key]))
Var _Lab = CALCULATE(SUM(Install_Input[LaborEarnings]),ST_PFP_Report[InvoiceBusinessUnitName]
IN {"Commercial Install","Commercial - Installs","Commercial Services - Buffalo"},
USERELATIONSHIP(Install_Input[PFPWeekly_Yearweeknum],ST_PFP_Report[Jobtime_Yearweeknum]))
Var _Install = IF(_Lab> 0, _Lab,_le * [#Rate For Work SPRT])
Return IF(_Install = BLANK(),0,_Install)
-----Using No Caluculate
Please correct my SUMMARIZE measure, or alternatively, suggest a low-performance measure. Your assistance would be greatly appreciated.
Commercial instal=
var LE_act= SUMMARIZE(filter(ST_Activity_Report,ST_Activity_Report[BusinessUnit]
IN {"Commercial Install","Commercial - Installs","Commercial Services - Buffalo"} )
,ST_Activity_Report[Indirect Key]
,"@sum",sum(ST_Activity_Report[PaidDuration]))
Var PFP_LE = sumx(VALUES(ST_PFP_Report[ID key]),
var FILTERED=FILTER(LE_act,[ID key]=[Indirect Key])
Return
sumx(FILTERED,[@sum]))
var Lab_act= SUMMARIZE(Install_Input,Install_Input[PFPWeekly_Yearweeknum]
,"@sum",sum(Install_Input[LaborEarnings]))
Var _Pfp_table = SUMMARIZE(FILTER(ST_PFP_Report,ST_PFP_Report[InvoiceBusinessUnitName]
IN {"Commercial Install","Commercial - Installs","Commercial Services - Buffalo"})
,ST_PFP_Report[Jobtime_Yearweeknum])
Var PFP_Lab = sumx(_Pfp_table,
var FILTERED=FILTER(Lab_act,[Jobtime_Yearweeknum]=[PFPWeekly_Yearweeknum])
Return
sumx(FILTERED,[@sum]))
Return IF(PFP_Lab > 0 ,PFP_Lab + 0,PFP_LE * [#Rate For Work SPRT] + 0)
Hi, @Learner27
Try this code. It should improve the performance.
Commercial Install =
Var _le =
CALCULATE (
SUM ( ST_Activity_Report[PaidDuration] ),
ST_Activity_Report[IsCommercialInstall],
USERELATIONSHIP ( ST_Activity_Report[Indirect Key], ST_PFP_Report[ID key] )
)
Var _Lab =
CALCULATE (
SUM ( Install_Input[LaborEarnings] ),
ST_PFP_Report[IsCommercialInstall],
USERELATIONSHIP ( Install_Input[PFPWeekly_Yearweeknum], ST_PFP_Report[Jobtime_Yearweeknum] )
)
Var _Install =
IF ( _Lab > 0, _Lab, _le * [#Rate For Work SPRT] )
Return
IF ( ISBLANK ( _Install ), 0, _Install )
Here, ST_Activity_Report[IsCommercialInstall] and ST_PFP_Report[IsCommercialInstall] are new calculated columns.
You can create IsCommercialInstall column like this:
IsCommercialInstall =
IF (
ST_Activity_Report[BusinessUnit] IN {"Commercial Install","Commercial - Installs","Commercial Services - Buffalo"},
TRUE,
FALSE
)
Proud to be a Super User!
User | Count |
---|---|
26 | |
10 | |
8 | |
6 | |
6 |
User | Count |
---|---|
31 | |
11 | |
10 | |
10 | |
9 |