Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
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.
Thank you!!
This worked perfectly, and I used it in various forms already!
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 ))
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:
That worked! there was one missing comma after the calculatetable line, but eventually I figured that out 🙂
Thank you! Much appreciated.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |