cancel
Showing results for
Did you mean:
Helper I

## counting how many had 2 specific values

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: counting how many had 2 specific values

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.

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

8 REPLIES 8
Super User IV

## Re: counting how many had 2 specific values

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.

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Helper I

## Re: counting how many had 2 specific values

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

Super User IV

## Re: counting how many had 2 specific values

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.

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Helper I

## Re: counting how many had 2 specific values

Thank you!!

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

Helper I

## Re: counting how many had 2 specific values

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

Super User IV

## Re: counting how many had 2 specific values

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 ))``````

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Helper I

## Re: counting how many had 2 specific values

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

Thank you! Much appreciated.

Highlighted
Helper I

## Re: counting how many had 2 specific values

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!

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors