Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
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!
Solved! Go to 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.
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.
Hi @Anonymous
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!
User | Count |
---|---|
77 | |
70 | |
69 | |
54 | |
48 |
User | Count |
---|---|
42 | |
37 | |
34 | |
31 | |
28 |