Building measure for Funnel showing steps that are completed

[cliu822]

Hello everyone, I'm trying to build a measure to build a Funnel chart.

The Funnel should display 10 verification steps. 

I have an example table for the verification steps like below:

Steps	Step Description	Step Level
Step1	Stop at Step1	1
Step2	Stop at Step2	2
Step3	Stop at Step3	3
Step4	Stop at Step4	4
Step5	Stop at Step5	5
Step6	Complete Step6	6
Step7	Complete Step7	7
Step8	Complete Step8	8
Step9	Complete Step9	9
Step10	Complete Step10	10

And a User table as below, where all the steps are in column "Attribute". The "Value" column is a column with binary value, showing if one step is complered or not. The tricky part is, 0 and 1 in this table serves as "No" and "Yes", so as the "Steps Table" above suggests, the description of step 1-5 are "Stop at Step1" etc., which makes a 0 for step 1-5 means a user has actually completed these steps and 1 means he didn't complete the step. As the description of step 6-10 are "Completed Step 6" etc., a value 1 means a user has actually completed these steps and 0 means he didn't complete the step.

Date	UID	Attribute	Value
2022-01-01	12345	Step1	0
2022-01-01	12345	Step2	0
2022-01-01	12345	Step3	0
2022-01-01	12345	Step4	0
2022-01-01	12345	Step5	0
2022-01-01	12345	Step6	1
2022-01-01	12345	Step7	1
2022-01-01	12345	Step8	1
2022-01-01	12345	Step9	0
2022-01-01	12345	Step10	0
2022-01-03	ABCDE	Step1	0
2022-01-03	ABCDE	Step2	0
2022-01-03	ABCDE	Step3	0
2022-01-03	ABCDE	Step4	1
2022-01-03	ABCDE	Step5	0
2022-01-03	ABCDE	Step6	0
2022-01-03	ABCDE	Step7	0
2022-01-03	ABCDE	Step8	0
2022-01-03	ABCDE	Step9	0
2022-01-03	ABCDE	Step10	0

Using the logic above, with the example data, there should be two users completed Step 1-3, and one user completed Step 4-8, no user completes the step 9-10.

Given the way the user table is constructed, how should I build a measure for the Funnel Chart, which display how many users complete each step?

Many thanks in advance!

[v-rzhou-msft · Accepted solution]

Hi @cliu822 ,

I recommend you to unify the format to confirm whether it is completed or not.

Yes/No = 
VAR _Description =
    RELATED ( Dim[Step Description] )
VAR _Condtion1 =
    AND ( CONTAINSSTRING ( _Description, "Stop" ), 'Table'[Value] = 0 )
VAR _Condtion2 =
    AND ( CONTAINSSTRING ( _Description, "Complete" ), 'Table'[Value] = 1 )
RETURN
    IF ( OR ( _Condtion1, _Condtion2 ), "Yes", "No" )

In my sample, I add a [Sort] column in Power Query to sort [Attribute] column.

Measure:

Count Complete = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[UID] ),
    FILTER ( 'Table', 'Table'[Yes/No] = "Yes" )
) + 0

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cliu822]

Hi @v-rzhou-msft 

Thank you very much for your answer, I have accepted it a solution. Later when I try I found a little problem: For User with UID "ABCDE", he stopped at Step4, it also means he didn't do step5. But in our system, since 0 for the first 5 steps means "user didn't stop at this step", so step 5 has a value of 0. But he actually didn't compelete this step, he even didn't start with it. So for the first 5 steps, we should only count all the 0 value, until there is a 1 value. How should I add this into the query? Thank you!