Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
xiumi_hou
Post Partisan
Post Partisan

urgent need help with dax measure

Hi there, 

 

I have a calculated measure called cancellation rate. However, I want to find the median value among the group, but do not consider those who do not have a cancellation rate. How to revise the below measure?

 

Group Assessment cancellation rate = MedianX(filter(f_calls,[Assessment cancellation rate]>=0),ALLSELECTED(d_users),[Assessment cancellation rate])

10 REPLIES 10
ADPowerBI1
Responsive Resident
Responsive Resident

Hi, please try this revised measure. Thanks!

 

Group Assessment cancellation rate = MedianX( FILTER( f_calls, NOT(ISBLANK([Assessment cancellation rate])) && [Assessment cancellation rate] >= 0 ), ALLSELECTED(d_users), [Assessment cancellation rate] )

@ADPowerBI1 Thank you for your reply. It shows like: Two many arguement were passed to MedianX, the man is 2. Do you have any guidance on how to revise this?

 

xiumi_hou
Post Partisan
Post Partisan

Inaddition to that, the filter is store under user table, group column and name column.

The relationship is users.id = calls.assigned_user_id

ppm1
Solution Sage
Solution Sage

MEDIANX is inherently slow as it needs to be done by the formula engine. However, you can improve your expression. What is the expression for your cancellation rate measure? Do you have more than one table in your model? Are you trying to get the median of all calls/rows? Or get the median of the rate for the values in another column or table (dimension)?

 

Pat

Microsoft Employee

@ppm1 Another comment is for the median, the number need to filter our the null or blank value.. but include cancellation rate which is 0.. Thank you!

Here is a better (more performant way to write one of your measure (do the same for the other). It is not good practice to FILTER a whole table in a CALCULATE (especially a big fact table).

 

Count_Assessment_Cancel/Missing =
CALCULATE (
    COUNT ( f_calls[Parent_id] ) + 0,
    f_calls[Status] IN { "Cancelled", "Missing", "Cancelled_24hr" },
    f_calls[Type_c] IN { "Assessment", "Screening" }
)

 

For the MEDIANX calculation, are you intending to evaluate your measure on every row of your calls table? If so, that is another bad practice to trigger context transition on every row of a large table. Hopefully you want to evaluate your measure for each value of a dimension column and take the MEDIAN of that. Please clarify. Sample data with expected output would be helpful.

 

Pat

Microsoft Employee

Hi @ppm1 ,

 

Thanks for the reply.  So in the call table, each row represent one phone call record. And each records as an id (who did this call) to link to the user table.

 

The call table include the call time, call status(held or not held or cancell etc) and call type.

I would like to know, for a given perid, what is the group cancellation rate?(One is the rate will not influence by which user table filter I chose) The second one is it will influence by the user group I choose, but not the individual users. Thank you!

Did you already rewrite the base measure as suggested? If so, how much did it speed things up. In any case, if I understand you correctly, the one that will respond to slicers would be

 

MEDIANX(VALUES(UserTable[UserColumn]), [Assessment cancellation rate])

 

And the one that would ignore slicers would be 

 

MEDIANX(ALL(UserTable[UserColumn]), [Assessment cancellation rate])

 

Pat

Microsoft Employee

@ppm1  Thanks for the reply. I will try the speed up measure. Can you help me with the below follow up question?

f I understand you correctly, the one that will respond to slicers would be

 

MEDIANX(VALUES(UserTable[UserColumn]), [Assessment cancellation rate])

Re: This is not correct, since some user show as NaN, some user show as 0, some user show as 1, I would like the value to calcuate based on the use[group], but not change on individual user user[name] filter

 

And the one that would ignore slicers would be 

 

MEDIANX(ALL(UserTable[UserColumn]), [Assessment cancellation rate])

Re: This is not correct, since all the value is showing NaN no matter which user I choose

 

Hi Pat, Please see my response below: @ppm1 

And I have two employee filters on the dashboard(one group, one employee name), I would like to find the median cancellation rate among the group:

1. I just want to find the median value across the entire group regardless of which group and people I selected

2.I want to find the median group value, for example, if I click group a, then the value will show group a median cancellation regardless of which people I selected 

 

Assessment cancellation rate = [Count_Assessment_Cancel/Missing]/[Count_assessment_Scheduled]
 
Count_Assessment_Cancel/Missing =
CALCULATE(
    COUNT(f_calls[Parent_id])+0,
   FILTER(
       f_calls,
       (f_calls[Status]="Cancelled" ||
       f_calls[Status]="Missing" || f_calls[Status]= "Cancelled_24hr" )&& (
       f_calls[Type_c]="Assessment" ||
       f_calls[Type_c]="Screening")
)
)
 
Count_assessment_Scheduled =
CALCULATE( COUNT(f_calls[Parent_id])+0,
   FILTER(
       f_calls,
        f_calls[Type_c]="Assessment" ||
      f_calls[Type_c]="Screening"    
)
)
 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.