cancel
Showing results for
Did you mean:
Frequent Visitor

## Counting dates based on specific text

Hi,

Can anyone help me in finding the count of dates based on the reference date given? The below example shows the Patients call flows. Row-1 shows the three calls scheduled to the women on alternate days, however, the Patient did not answer a single call on that day.  What I expect from it is, (as shown in Result table given below), I need to count for each patient, how many calls scheduled in the given month, and out of which, no.of times the patient answered in that particular month.  All these are based on date count only. Please help in finding out the solution.

 PID Call_Schedued_Date-1 Call_Schedued_Date-2 Call_Schedued_Date-3 Call_answered_date-1 Call_answered_date-2 Call_answered_date-3 V03861 04/04/2017 06/04/2017 08/04/2017 V03861 14/03/2017 16/03/2017 18/03/2017 16/03/2017 18/03/2017 V03861 07/03/2017 09/03/2017 11/03/2017 07/03/2017 09/03/2017 V03861 28/02/2017 02/03/2017 04/03/2017 04/03/2017 V03861 21/02/2017 23/02/2017 25/02/2017 V03861 14/02/2017 16/02/2017 18/02/2017 V03861 07/02/2017 09/02/2017 11/02/2017 V03861 31/01/2017 02/02/2017 04/02/2017 V03861 24/01/2017 26/01/2017 28/01/2017 V03861 17/01/2017 19/01/2017 21/01/2017

Result table:

 Calls scheduled Calls answered Patient ID Jan'17 Feb'17 Mar'17 Apr'17 Jan'17 Feb'17 Mar'17 Apr'17 V03861 7 12 8 3 0 0 5 0

Regards,

Prashanth SJ

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Counting dates based on specific text

Hi Prashanth,

It could be 1:*. Did you try it? Can you share the pbix file? The 1 side of the relationship should have unique values.

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Community Support Team

## Re: Counting dates based on specific text

Hi Prashanth,

There could be two solutions. Please refer to the attachment for details. One solution is as follows.

1. Pivot the table.

2. Create two measures.

```Call Answered 1 =
CALCULATE (
COUNT ( SolutionOne[Calls] ),
FILTER ( 'SolutionOne', FIND ( "Call_answered", [Calls],, 0 ) > 0 )
)
```
```Call Scheduled 1 =
CALCULATE (
COUNT ( SolutionOne[Calls] ),
FILTER ( 'SolutionOne', FIND ( "Call_Schedued", [Calls],, 0 ) > 0 )
)
```

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Counting dates based on specific text

Hi Dale,

Thanks so much for your quick response on the query. It is helpful.

I tried to apply the same logic to my dataset. However, I am getting an error while doing it so for ''Calendar[date]'' table. Can I have any solution for this...?

When I am trying to add the relations (1-1) with 'Calendar[date] and (Scheduled & Answered call data fields). The below screenshot error I am getting. Please see.

Regards,

Prashanth

Community Support Team

## Re: Counting dates based on specific text

Hi Prashanth,

It could be 1:*. Did you try it? Can you share the pbix file? The 1 side of the relationship should have unique values.

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

Hi,

Hope this helps.

Frequent Visitor

## Re: Counting dates based on specific text

Dear Dale,

Thanks a ton, it worked well. I could able to apply the same logic with some minor changes as per my requirement.

Regards,

Prashanth

Frequent Visitor

## Re: Counting dates based on specific text

Hi Dale,

I have one more requirement which will ease my regular work. Would you help me in this regard?. This is regarding the Unique Calls Scheduled and Answered. I tried pivoting it, but I am getting totals. I am expecting this to be in a way that, it should give  Unique days and subjects.

Logic would be if a person (Subject) is scheduled three calls in a day, in which anyone call answered by the person in that particular day is counted as one 'answered call'. If a person fails to attempt three calls in that day will be treated as 'Un-answered call'

Unique Call analysis.pbix

Hope this table explains clearly about my required.

 Output data table : PID/Subjects Number of Subject call Days Answered Calls Un-answered Calls DUMMY101 13 8 5 DTDUMMYBG 3 1 2

Regards,

Prashanth

Community Support Team

## Re: Counting dates based on specific text

Hi Prashanth,

Since it's a new requirement, I would suggest you create a new thread in this forum with the full descriptions and the sample file.

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Counting dates based on specific text

Hi Dale,
Sure, will do that.

Prashanth