Forum Discussion

xiumi_hou's avatar
xiumi_hou
Post Partisan
5 years ago

Need urgent help!! please. dax function.

Hi All,

 

I have three tables, user table which store all the employee name, case table which store all the client information, call table which store all the phone call information. One employee will receive mutiple clients(cases), and one client(case) will have multiple calls.

 

Goal: I want to calculate for each month, each employee, what is the number of clients that schedule its first phone within the same month as they assigned to the employee. For example, if the person assigned to employee on Nov 27 and scheduled first call on Nov 29, this will count into 1; if the person assigned to employee on Nov 27 and scheduled first call on Dec, this will not consider as the same month.

 

And my dax fuction is as below:

This is to calculate the first scheduled calls date (This not show the correct informaiton)

FirstScheduledDate =
CALCULATE(
MIN(f_calls[date_start]),
FILTER(f_calls,
f_calls[Type_c]="Screening" || f_calls[Type_c]="Assessment"
),
ALLEXCEPT(d_cases,d_cases[id]))
 
This is to calcuate how many cases has the first scheduled calls within the same month
Measure 2 = calculate(count(d_Cases[CaseNumber]),FILTER(d_Cases,month(d_Cases[Assigned_to_date_c])=month([FirstScheduledDate])&&year(d_Cases[Assigned_to_date_c])=year([FirstScheduledDate])))
Need Help

3 Replies

  • xiumi_hou's avatar
    xiumi_hou
    Post Partisan
    5 years ago

    It seems like the first scheduled date is not count for each cases...but among all the cases.... anyone can help? urgent. Thanks!!!

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    xiumi_hou ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi xiumi_hou ,

     

    Please show more information, it's hard to create sample data and DAX formula only by the above description.

     

    Best Regards,

    Jay