Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
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!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
90 | |
83 | |
75 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |