cancel
Showing results for
Did you mean:
Highlighted
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.

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

Not the Power BI thought police...

##### I have 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.

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

Not the Power BI thought police...

##### I have 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.

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

Not the Power BI thought police...

##### I have 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 ))``````

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

Not the Power BI thought police...

##### I have 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.

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

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors