Forum Discussion

yashwant101's avatar
yashwant101
Helper III
2 years ago

Measure Optimization

Hi all,

 

I have created 3 measures, one dependent on the other. Below are the measures:

 

Last Status HCP =
VAR _MaxDt = CALCULATE(MAX(rpt_fbp_cust_list_hcp1[txn_dt]), ALL(rpt_fbp_cust_list_hcp1[ptnt_stat]))
VAR _MaxStat = CALCULATE(MAX(rpt_fbp_cust_list_hcp1[ptnt_stat]), rpt_fbp_cust_list_hcp1[txn_dt]=_MaxDt,rpt_fbp_cust_list_hcp1[ptnt_id]<>BLANK() && rpt_fbp_cust_list_hcp1[ptnt_stat]<>BLANK())
RETURN
_MaxStat

 

Active Patient HCP =
CALCULATE(DISTINCTCOUNT(rpt_fbp_cust_list_hcp1[ptnt_id]),FILTER(SUMMARIZE(rpt_fbp_cust_list_hcp1,rpt_fbp_cust_list_hcp1[ptnt_id],"last status",[Last Status HCP]),[last status]="ACTIVE"))


Count Active Patients HCP = CALCULATE([Active Patient HCP], ALL(rpt_fbp_cust_list_hcp1[txn_dt]), rpt_fbp_cust_list_hcp1[txn_dt]<=MAX(rpt_fbp_cust_list_hcp1[txn_dt]), ALL(rpt_fbp_cust_list_hcp1[Month End Date Sort]), ALL(rpt_fbp_cust_list_hcp1[Month End]))

 

I am using the final measure in my table. Without this measure the table is working smoothly. But as soon as I put this measure in the table, it becomes very slow. I even tried using the first two in the table but they are working fine. The last one is just killing my table. Please lt me know as to how I can optimize these measures.

 

Any help is highly appreciated!

 

Reards,

Yashwant

Need Help
Show and Tell

3 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi yashwant101  try new version for Active Patient HCP

    Active Patient HCP=

    VAR __table=
    FILTER(SUMMARIZE(rpt_fbp_cust_list_hcp1,rpt_fbp_cust_list_hcp1[ptnt_id],
    "last status",[Last Status HCP]),[last status]="ACTIVE")
    RETURN
    CALCULATE(DISTINCTCOUNT(rpt_fbp_cust_list_hcp1[ptnt_id]),
    __table))

    • yashwant101's avatar
      yashwant101
      Helper III
      2 years ago

      Hi some_bih ,

       

      Thanks for your response!

       

      I tried the measure that you provided, but still it is taking a lot of time, in fact the time increased. And as I said, the last measure is taking a lot of time. Please let me know if there is any way to reduce the time there.

       

      Regards,

      Yashwant