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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.