Forum Discussion

Learner27's avatar
Learner27
Icon for Helper III rankHelper III
3 years ago

Correct my Summarize Measure

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)

 

1 Reply

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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
    )