Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
cliu822
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:

StepsStep DescriptionStep Level
Step1Stop at Step11
Step2Stop at Step22
Step3Stop at Step33
Step4Stop at Step4

4

Step5Stop at Step55
Step6Complete Step66
Step7Complete Step77
Step8Complete Step88
Step9Complete Step99
Step10Complete Step1010

 

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.

 

DateUIDAttributeValue
2022-01-0112345Step10
2022-01-0112345Step20
2022-01-0112345Step30
2022-01-0112345Step40
2022-01-0112345Step50
2022-01-0112345Step61
2022-01-0112345Step71
2022-01-0112345Step81
2022-01-0112345Step90
2022-01-0112345Step100
2022-01-03ABCDEStep10
2022-01-03ABCDEStep20
2022-01-03ABCDEStep30
2022-01-03ABCDEStep41
2022-01-03ABCDEStep50
2022-01-03ABCDEStep60
2022-01-03ABCDEStep70
2022-01-03ABCDEStep80
2022-01-03ABCDEStep90
2022-01-03ABCDEStep100

 

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!

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
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.

RicoZhou_0-1674119166476.png

Measure:

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

Result is as below.

RicoZhou_1-1674119308926.png

 

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.

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
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.

RicoZhou_0-1674119166476.png

Measure:

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

Result is as below.

RicoZhou_1-1674119308926.png

 

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 @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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.