counting how many had 2 specific values

[EF]

Hi all,

I think this is a simple DAX measure (or 2) but as usual DAX stumps me...

I have a Facts table where each record is an event.
A client can have many event types.
I want a measure to count how many clients have received both event Step1 and Step2,
and calculate the percentage of clients who had Step2 following Step1, out of all Step1.

event types: Call, Intake, Assign, Schedule, Discharge.
TypeA = Call OR Intake
TypeB = Assign OR Schedule
note- Discharged is neither.

Example:

Columns: Date__clientID__EventType

1/1/20__ClientA__Call
1/1/20__ClientB__Call
1/2/20__ClientA__Intake
1/2/20__ClientC__Intake
1/2/20__ClientA__Assign
1/3/20__ClientC__Assign
1/3/20__ClientD__Schedule
1/4/20__ClientE__Discharge
1/4/20__ClientC__Schedule

Result =
Number of Clients with Step1 AND Step2 = 2 (ClientA and ClientC)
% of clients whose Step1 resulted in Step2 = 66% (clientA, ClientB, ClientC all had Step1 but only 2/3 had Step2)

Hope this makes sense.

Thanks

[Greg_Deckler]

Almost, I take it that Step 1 is Intake? And Step 2 is Assign? Is that really your source data? Likely need to split your column into 3 if that is the case. Once you do that, should be able to get the win using something like EXCEPT and some table variables that FILTER your base table.

[EF]

Hi @Greg_Deckler 

Step1 is  Call and/or Intake (client can have 1 or both),

similarly Step2 is Assign and/or Schedule.

To make this less complicated I can add a calculated column = IF ([eventtype] = "Call" || [eventtype] = "Intake", "Step1", IF ([eventtype]="Assign" || [eventtype] = "Schedule", "Step2".

Then I will have a column of Step1, Step2 or null.

Can you give me a DAX measure to say: how many clients have Step 1 AND Step2

and: What percentage of Clients have Step1 and Step2 out of all Clients that have Step1

[Greg_Deckler · Accepted solution]

It's probably something along the lines of:

This is a measure for counting the number of customers with Step 1 and Step 2 = 
    VAR __Table = 
            SUMMARIZE(
                'Table',
                [Client],
                "Step 1",COUNTROWS(FILTER('Table',[Client] = EARLIER([Client]) && [Step] = "Step 1")),
                "Step 2",COUNTROWS(FILTER('Table',[Client] = EARLIER([Client]) && [Step] = "Step 2"))
            )
RETURN
    COUNTROWS(FILTER(__Table,[Step 1] > 0 && [Step 2] > 0 ))

I attached a PBIX. To get the percentage, just divide this measure by a COUNTROWS of DISTINCT [Clients].

If you run into problems, I can take a look at it tomorrow, I've had a fair bit of Scotch and need to call it a night.

[EF]

Thank you!!

This worked perfectly, and I used it in various forms already!

[EF]

Hi @Greg_Deckler 

Another question on this. Can you adjust the measure so that it does not get affected by date slicers?

I do want to slice it by other slicers/filters but not by date.

Thanks,

EF

[Greg_Deckler]

Sure, you would do something along the lines of:

This is a measure for counting the number of customers with Step 1 and Step 2 = 
    VAR __Table = 
            SUMMARIZE(
                CALCULATETABLE('Table',REMOVEFILTERS('Date'[Date]))
                [Client],
                "Step 1",COUNTROWS(FILTER('Table',[Client] = EARLIER([Client]) && [Step] = "Step 1")),
                "Step 2",COUNTROWS(FILTER('Table',[Client] = EARLIER([Client]) && [Step] = "Step 2"))
            )
RETURN
    COUNTROWS(FILTER(__Table,[Step 1] > 0 && [Step 2] > 0 ))

[EF]

Hi @Greg_Deckler 

I need to add a layer of complexity to this measure that I am hoping you can once again help me with.

I agree that EARLIER is a terrific function, I wish I understood how it works better!

I used a version of this measure to create a WaitList: Clients whohad Step 1 (Intake) but not step 2 (assigned), using this code:

WaitList = VAR __Table =

SUMMARIZE(

CALCULATETABLE(EventTable,REMOVEFILTERS(DateTable[Date])),

[client],

"Step 1",COUNTROWS(FILTER(EventTable,[client] = EARLIER([client]) && [step] = "Step 1" )),

"Step 2",COUNTROWS(FILTER(EventTable,[client] = EARLIER([client]) && [step] = "Step 2"))

)

RETURN

COUNTRows(FILTER(__Table,([Step 1] > 0 && [Step 2] = 0 )))

works well.

However, a client can go through the system more than once. If that happens, the above measure will find that Step 2 is greater than 0 because they have been assigned in the past, but they are really back in Intake and on the waiting list to be assigned.

What would you suggest? I created a table in Query Editor to number the episodes of care ([Client]/[index]/[episode#] where each Step 1 begins a new episode), but not sure how to bring that into this calculation.

Thank you for your continued assistance!

[EF]

That worked! there was one missing comma after the calculatetable line, but eventually I figured that out 🙂

Thank you! Much appreciated.