Measure Optimization

[yashwant101]

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

[some_bih]

Hi @yashwant101 it is hard to provide additional,  better solution without model, file.

[some_bih]

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]

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