Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Hi,
You may download my PBI file from here.
Hope this helps.
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
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
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
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
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'
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 month | DUMMY101 (13 Unique subject call days; DTDUMMYBG (3 Unique subject call days) Total: 16 Unique Subejct Call Days |
Answered Calls | Call 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 Calls | Un-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 Calls | Un-answered Calls |
DUMMY101 | 13 | 8 | 5 |
DTDUMMYBG | 3 | 1 | 2 |
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |