cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neelofarshama
Post Partisan
Post Partisan

Funnel with Array data set in Power BI

Hi,

 

I have a dataset with array column and user id column as shown below. A funnel chart is required to be plotted for step vs count user_id  in Power BI. The expected output is also provided below. The requirement is to check the step column for steps landingpage, registration_wall, logged_in, payment_details_provided, purchase_submitted if any of these steps is missing then count cannot be calculated. I have aadded an extra column to show count and cannot count if any step is missing. Please help me achieve this.

user_uuidstep 
1  
1landingpagecounted
1registration_wallcounted
1logged_incounted
1payment_details_providedcounted
1purchase_submittedcounted
2  
2  
2landingpagecounted
2registration_wallcounted
2logged_incounted
2payment_details_providedcounted
2purchase_submittedcounted
3  
4  
4  
4landingpagecounted
4registration_wallcounted
4logged_incounted
5  
5landingpage(cannot be counted)
6  
6landingpagecounted
6registration_wallcounted
6landingpage(cannot be counted as logged_in should be in this step)
7  
7  
7landingpagecounted
7logged_in(cannot be counted as registration_wall should be in this step)
7payment_details_provided(cannot be counted)
7purchase_submittedcounted

 

Expected output:

Funnel stepsCount User_id 
landingpage6
registration_wall4
logged_in3
payment_details_provided2
purchase_submitted2

 

Thanks in Advance,

Neelofar.

5 REPLIES 5
v-janeyg-msft
Community Support
Community Support

Hi, @neelofarshama 

 

According to your description, I create a sample.

You need to create a index column in PQ first.

vjaneygmsft_1-1639121438672.png

Then create two columns to display the result you want.

Like this:

Column = 
VAR a1 = "landingpage"
VAR a2 = "registration_wall"
VAR a3 = "logged_in"
VAR a4 = "payment_details_provided"
VAR a5 = "purchase_submitted"
VAR tablea =
    FILTER (
        'Table',
        [Index]
            = EARLIER ( 'Table'[Index] ) - 1
            && [user_uuid] = EARLIER ( 'Table'[user_uuid] )
    )
VAR tableb =
    FILTER (
        'Table',
        [Index]
            = EARLIER ( 'Table'[Index] ) + 1
            && [user_uuid] = EARLIER ( 'Table'[user_uuid] )
    )
VAR b1 =
    MAXX ( tablea, [step] )
VAR b2 =
    MAXX ( tableb, [step] )
VAR b = [step]
RETURN
    IF (
        b = a1
            && b1 = BLANK (),
        1,
        IF (
            b = a2
                && b1 = a1,
            1,
            IF (
                b = a3
                    && b1 = a2,
                1,
                IF ( b = a4 && b1 = a3, 1, IF ( b = a5 && b1 = a4, 1 ) )
            )
        )
    )
Column 2 = 
VAR a =
    MINX (
        FILTER (
            'Table',
            [step] <> BLANK ()
                && [Column] = BLANK ()
                && [user_uuid] = EARLIER ( 'Table'[user_uuid] )
        ),
        [Index]
    )
RETURN
    IF ( a = BLANK (), [Column], IF ( [Index] > a, BLANK (), [Column] ) )

Then count column2.

vjaneygmsft_0-1639121407391.png


Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

 

Best Regards,
Community Support Team _ Janey

 

v-janeyg-msft
Community Support
Community Support

Hi, @neelofarshama 

 

The logic you wrote seems to contradict the result displayed in the screenshot. Can you explain it?

vjaneygmsft_3-1638425718743.png

 

vjaneygmsft_2-1638425686339.png

 

vjaneygmsft_1-1638425678500.png

If you can explain it clearly, we can help you soon.

 

Best Regards,
Community Support Team _ Janey

 

Hi @Community Support Team _ Janey,

Thank you for the reply. Sorry it was a typo the columns which you have highlighted are counted so that it matches the screenshot result.

@neelofarshama 

 

I don't understand what you mean, so what should be the excepted results?

Hi,

I have data which has columns steps and user_id as shown below

user_uuidstep
1 
1landingpage
1registration_wall
1logged_in
1payment_details_provided
1purchase_submitted
2 
2 
2landingpage
2registration_wall
2logged_in
2payment_details_provided
2purchase_submitted
3 
4 
4 
4landingpage
4registration_wall
4logged_in
5 
5landingpage
6 
6landingpage
6registration_wall
6landingpage
7 
7 
7landingpage
7logged_in
7payment_details_provided
7purchase_submitted

 

I should plot a funeel chart with these columns steps Vs count of user_id for which expected output should be

StepsCount of user_id
landingpage6
registration_wall4
logged_in3
payment_details_provided2
purchase_submitted2

 

But the output I am getting is 

landingpage7
registration_wall4
logged_in4
payment_details_provided3
purchase_submitted3

 

This is because below steps should be in shown sequence if any one step is missed it cannot be calculated.

{landingpage}
{landingpage, registration_wall}
{landingpage, registration_wall,logged_in}
{landingpage, registration_wall,payment_details_provided}
{landingpage, registration_wall,payment_details_provided,purchase_submitted}

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!