cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EF Helper I
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
Highlighted
Super User IV
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!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Super User IV
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!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

EF Helper I
Helper I

Re: counting how many had 2 specific values

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

 

Highlighted
Super User IV
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!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

EF Helper I
Helper I

Re: counting how many had 2 specific values

Thank you!!

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

EF Helper I
Helper I

Re: counting how many had 2 specific values

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

Super User IV
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!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

EF Helper I
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.

EF Helper I
Helper I

Re: counting how many had 2 specific values

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!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.