Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EF
Helper II
Helper II

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

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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you!!

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

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

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!

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

 

Thank you! Much appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors