topic Re: FIRST CALL RESOLUTION % in DAX Commands and Tips

FIRST CALL RESOLUTION %

Trojden — Tue, 14 Jul 2020 09:35:27 GMT

Hi,

I wan to create a measure for obtaining FCR from my call log.

In the call log I have it has unique callkey, not uniqe: casekay and statuskey paired with that call and indicator of direction (Outgoing/Ingoing).
I need to show: count of callkeys with specific status key, count of statuses = 1 for a distinct case, and direction = "Outgoing".

How could I do that?
I have separate dim for statuses, call table is my fact table. Cases are within the call table for now.

Re: FIRST CALL RESOLUTION %

amitchandak — Tue, 14 Jul 2020 09:57:04 GMT

Trojden , data you share is not sufficient to suggest formula.

Can you share sample data and sample output in table format?

Re: FIRST CALL RESOLUTION %

Trojden — Tue, 14 Jul 2020 11:51:36 GMT

amitchandak My sql querries which are working:

select count(CaseKey) as FCRCOUNT from CALLS where CaseKey IN ( select CaseKey from POWER_BI_AGENTS_OUTGOING_CALLS group by CaseKey HAVING COUNT(WorkflowStateKey) < 2) AND WorkflowStateKey = '2313E516-3568-462B-A61A-E984199DD093' AND Direction = 'Outgoing') select count(*) as TotalCount from CALLS FCR % would be a division of FCRCOUNT by TotalCount

Sample data:

CallKey CaseKey WorkflowStateKey Direction Connect BC6C7FB8-A36C-CB73-AC34-00E62BF264E7 44E2CDD8-A6D1-4CB7-805D-2D83487C90EF 514E5977-7DE8-48D4-AF00-6A7AA795EAB1 outgoing 0 3A9AF9E4-7961-E248-142D-00E76B872F33 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 1 7C85C5BC-E280-E978-4883-00EC66BE313D 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 1 B0152FFA-6A1D-F236-9B3D-00EF870BA2EC 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 incoming 1 651B9477-D12F-1E1D-8144-00F13F2B0FB0 B2825F29-6769-4D25-BBC2-042E500AB115 1B9DD1AE-CAF4-4012-A6EE-0E385F6FA2E6 outgoing 1 E472B200-868F-1AC4-9909-00F74BD59B40 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 incoming 1 F1E37D16-1DFE-DC5E-FB79-00F863B0A845 CDB1CC8C-A907-4539-B8DB-C01243893271 CC95D4B4-0076-474F-81E8-C7591A366BB2 outgoing 1 C5F45AE0-7002-4116-8494-00F897C0C342 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 0 F6730D3C-523E-B840-ACD9-00FC90EF58DF 8D3B68B1-470B-48D0-B47C-B445B1C2B0D5 00000000-0000-0000-0000-000000000000 outgoing 0 2CF5E853-C9E7-6198-6A28-01004D555C09 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 0 EBDDE58B-1B89-19DE-8CA4-0101A13B75A5 23423813-0C13-48A9-97A8-2D4368944D02 2313E516-3568-462B-A61A-E984199DD093 outgoing 1 B26D6268-5769-5023-09C9-01049748D4BD 369A101E-FEE2-4EAE-86BD-5FA52D745080 0661993C-B04F-41FD-9906-95AD96356A02 outgoing 0 2B5C8C76-D8A1-773F-AF09-0104AEEB573A 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 incoming 0 8E793057-CB5A-492D-0AEF-0105920C40FB 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 incoming 1 1C760FD8-B652-E9E2-1E8C-0105E4AFAC03 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 0 3BA60361-DB5E-0912-D441-0108E5D3F9D0 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 1 AFE80CEA-104F-61BC-30D6-010D3096C775 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 0 963DA531-2707-D6A8-42B1-01145AE46447 9CADA981-C63E-45BF-9BC3-26981DB586E3 514E5977-7DE8-48D4-AF00-6A7AA795EAB1 outgoing 1

Re: FIRST CALL RESOLUTION %

Trojden — Wed, 15 Jul 2020 09:15:13 GMT

amitchandak

I have prepared better T-SQL, have a look:

declare @FCRCOUNT decimal (18,2) declare @totalCalls decimal (18,2) declare @FCRpercentage decimal (18,2) set @FCRCOUNT =( select COUNT(*) from (select CaseKey, max(WorkflowStateKey) Workflow,count(*) Workflow_COUNT, Direction from POWER_BI_AGENTS_OUTGOING_CALLS group by CaseKey, Direction HAVING COUNT(*) =1) FCR inner join POWERBI_WORKFLOW_STATES WS on WS.WorkflowStateKey = FCR.status where WS.Qualifier =5 AND FCR.Direction = 'Outgoing') set @totalCalls = (select count(*) from POWER_BI_AGENTS_OUTGOING_CALLS where Direction = 'Outgoing') set @FCRpercentage=(@FCRCOUNT / @totalCalls) select FORMAT(@FCRpercentage,'P0') as FCR