How to count number of clients that have 2 items in a specific order

[Anonymous]

Hello community members, 

I have to following issue: 

I would like to know the number of clients that 'receive' a Healthcare_Type "A" as well as Healthcare_Type "B" (specific in that particular order; first A, than B). Does anyone know how I should make this measure?

Hope to hear soon from you. 

Best regards, 

Sander

[AlexisOlson · Accepted solution]

It's not completely clear how first A then B logic works (e.g. what about ABA or BAB?) but I'll assume taking the first start date of each type is sufficient.

A then B Count =
VAR Summary =
    SUMMARIZE (
        Table1,
        Table1[Client_Number],
        "FirstA", CALCULATE ( MIN ( Table1[Start_date] ), Table1[Healthcare_type] = "A" ),
        "FirstB", CALCULATE ( MIN ( Table1[Start_date] ), Table1[Healthcare_type] = "B" )
    )
RETURN
    COUNTROWS (
        FILTER (
            Summary,
            NOT ( ISBLANK ( [FirstA] ) || ISBLANK ( [FirstB] ) )
                && [FirstA] < [FirstB]
        )
    )

This summarizes, for each client, the first Start_date for type A and type B (returning a summary table with one client per row) and then filters this summary table for clients where neither FirstA nor FirstB is blank and FirstA < FirstB.

[Anonymous]

Hi @AlexisOlson, Thank you! This measure works perfect! Cheers, Sander

[Jihwan_Kim]

Hi, @Anonymous 

Please try to use the below calculated measure in a Card Visualization.

Card Visualization =
VAR Atype =
CALCULATETABLE (
VALUES ( 'Table'[Client_Number] ),
'Table'[Healthcare_Type] = "A"
)
VAR Btype =
CALCULATETABLE (
VALUES ( 'Table'[Client_Number] ),
'Table'[Healthcare_Type] = "B"
)
RETURN
COUNTROWS ( INTERSECT ( Atype, Btype ) )

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos!!

[Anonymous]

Hi @Jihwan_Kim 

Thanks for your response, but the measure isn't correct... Because the measure also takes the combination First B and Second A (client 1003) and that it not correct...

Cheers, Sander