Count if relating to a specific service

[elinevans]

Hello, 

I am building a report to see how many client were contacted wihtin 48 hours of referral when referred into service A, however if they were contacted through any other service in the 48hrs period then that would count as the client being contacted on behalf of service A. 

I have 2 tables like the following

Client ID	Service
1234	A
3214	A
3214	B
2341	C
2145	B

Client ID	Service	Contact Date	Contacted in 48
1234	A	12/12/2020	True
3214	A	01/12/2020	False
3214	B	30/11/2021	True
2145	B	01/12/2020	True

I want to have a distinct count of clients that have been contacted in 48hrs, but they have to be associated with Service A. For example, Client 3214 was not contacted within 48hrs within service A but they were contacted through service B, and because they are also in service A this would be counted. However, 2145 would not be counted as they are not in service A. 1234 would be counted becuase they have been contacted in 48hrs and are in service A.

Something along the lines of a distinct count of clients in any service who have been contacted in 48hrs only if they are also a part of service A. 

Hope this makes sense

Thanks

Elin

[smpa01 · Accepted solution]

@elinevans  you can use a measure like this

Measure = 
VAR _left =
    SUMMARIZE (
        CALCULATETABLE (
            'Table 1',
            'Table 1'[Service] IN { "A" },
            ALLEXCEPT ( 'Table 1', 'Table 1'[Client ID] )
        ),
        'Table 1'[Client ID]
    )
VAR _right =
    SUMMARIZE (
        FILTER ( 'Table 2', 'Table 2'[Contacted in 48] = TRUE () ),
        'Table 2'[Client ID]
    )
RETURN
    COUNTX ( INTERSECT ( _left, _right ), 'Table 1'[Client ID] )

[Anonymous · Accepted solution]

Hi @elinevans ,

This is a measure to count the clients for specific service.

Measure = CALCULATE(DISTINCTCOUNT(TableB[Client ID]),
            FILTER(ALL(TableB),
                (TableB[Service]="A"&&TableB[Contacted in 48]="True")||
                (CALCULATE(MAX(TableB[Contacted in 48]),ALLEXCEPT(TableB,TableB[Client ID]))="True"&&"A" in CALCULATETABLE(VALUES(TableB[Service]),ALLEXCEPT(TableB,TableB[Client ID])))
                    ))

 

You could create a slicer and replace the "A" to SELECTEDVALUE('slicer'[service]), then you can calculate for the specified service.

Best Regards,

Jay

[VahidDM]

Hi @elinevans 

This is my suggestion:

Measure 2 = 
Var _A = 
filter(ADDCOLUMNS(SUMMARIZE('Table (2)','Table (2)'[Client ID],"Service",MIN('Table (2)'[Service])),"T/F",CALCULATE(COUNTROWS('Table (2)'),'Table (2)'[Contacted in 48]=true())),[Service]="A")
return
COUNTROWS(_A)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[smpa01 · Accepted solution]

@elinevans  you can use a measure like this

Measure = 
VAR _left =
    SUMMARIZE (
        CALCULATETABLE (
            'Table 1',
            'Table 1'[Service] IN { "A" },
            ALLEXCEPT ( 'Table 1', 'Table 1'[Client ID] )
        ),
        'Table 1'[Client ID]
    )
VAR _right =
    SUMMARIZE (
        FILTER ( 'Table 2', 'Table 2'[Contacted in 48] = TRUE () ),
        'Table 2'[Client ID]
    )
RETURN
    COUNTX ( INTERSECT ( _left, _right ), 'Table 1'[Client ID] )