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
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.

 

 


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

Not the Power BI thought police...

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


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

Not the Power BI thought police...

I have 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!

Highlighted
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

 

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.

 

 


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

Not the Power BI thought police...

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

 


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

Not the Power BI thought police...

I have 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
New Ranks Launched March 24th!

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!

‘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

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.

Community Summit North America

Community Summit North America

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

Top Solution Authors