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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Prashanth_SJ
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 IDJan'17Feb'17Mar'17Apr'17 Jan'17Feb'17Mar'17Apr'17
V0386171283 0050

 

 

Regards, 

Prashanth SJ

 

1 ACCEPTED SOLUTION

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.

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-jiascu-msft
Employee
Employee

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 )
)

Counting_dates_based_on_specific_text

 

 

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.

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.

 

Untitled.jpg

 

Regards,

Prashanth

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.

Dear Dale, 

 

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

 

Calls_listened%_yr wise.jpg

 

Regards, 

Prashanth

Hi Dale, 

 

Thanks for your help Mr.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.  

Ind.Logic
(Numerator / Denominator)
Expected Output for the month of March-2018 ….
(Example)
No.of Subjects
(n= no of persons receiving calls in the month)
Total unique count of women in that particular month.2
(PIDs: DUMMY101, DTDUMMYBG)
Number of Subject call Days
(Unique calls scheduled)
Sum of Answered & Un-answered; This shows number Unique calls scheduled to each person in that particular monthDUMMY101 (13 Unique subject call days;
DTDUMMYBG (3 Unique subject call days)

Total: 16 Unique Subejct Call Days
Answered CallsCall Report:
Col: Inbound/outbound = "OUTBOUND";
Col: Status of call = "COMPLETED", (and/or) "IN_PROGRESS";
Col: Successful = "TRUE"

Logic: As per above logic, if a woman has atleast 1 successful login (1 of 3 attempts) attempt in a day to be counted as One Answered call) against to each subject
DUMMY101 - 8 Unique Calls Answered in Mar-2018;

DTDUMMYBG - 1 Unique Call answered in Mar-2018;

Total: 9 Unique answered Call days
Un-answered CallsUn-answered calls  = No.of Subject call days - Answered calls)
[[Other than "Answered Calls"]]
Logic: if a woman failed to log in atleast once in that day (0 of 3 attempts) can be considered as the Un-answered call;
DUMMY101 - 5 Unique Calls Un-answered;
DTDUMMYBG -2 UniqueCalls Un-answered;

Total: 7 Un-answered call days;

 

 

Output data table :  
PID/
Subjects
Number of Subject call Days Answered CallsUn-answered Calls
DUMMY1011385
DTDUMMYBG312

 

Regards,

Prashanth

 

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.

Hi Dale, 
Sure, will do that. 
 
Prashanth

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.