cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Building measure for Funnel showing steps that are completed

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?

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

New Member

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!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors