Funnel with Array data set in Power BI

Question

Hi,&nbsp;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&nbsp; in Power BI. The expected output is also provided below. The requirement is to check the step column for steps landingpage, registration_wall,&nbsp;logged_in,&nbsp;payment_details_provided,&nbsp;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&nbsp;1&nbsp;&nbsp;1landingpagecounted1registration_wallcounted1logged_incounted1payment_details_providedcounted1purchase_submittedcounted2&nbsp;&nbsp;2&nbsp;&nbsp;2landingpagecounted2registration_wallcounted2logged_incounted2payment_details_providedcounted2purchase_submittedcounted3&nbsp;&nbsp;4&nbsp;&nbsp;4&nbsp;&nbsp;4landingpagecounted4registration_wallcounted4logged_incounted5&nbsp;&nbsp;5landingpage(cannot be counted)6&nbsp;&nbsp;6landingpagecounted6registration_wallcounted6landingpage(cannot be counted as logged_in should be in this step)7&nbsp;&nbsp;7&nbsp;&nbsp;7landingpagecounted7logged_in(cannot be counted as registration_wall should be in this step)7payment_details_provided(cannot be counted)7purchase_submittedcounted&nbsp;Expected output:Funnel stepsCount User_id&nbsp;landingpage6registration_wall4logged_in3payment_details_provided2purchase_submitted2&nbsp;Thanks in Advance,Neelofar.

v-janeyg-msft · Answer

Hi,&nbsp;Anonymous&nbsp;
&nbsp;
The logic you wrote seems to contradict the result displayed in the screenshot. Can you explain it?

&nbsp;

If you can explain it clearly, we can help you soon.
&nbsp;
Best Regards,Community Support Team _ Janey
&nbsp;

anonymous · Answer

Hi&nbsp;@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.

v-janeyg-msft · Answer

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

anonymous · Answer

Hi,I have data which has columns steps and user_id as shown belowuser_uuidstep1&nbsp;1landingpage1registration_wall1logged_in1payment_details_provided1purchase_submitted2&nbsp;2&nbsp;2landingpage2registration_wall2logged_in2payment_details_provided2purchase_submitted3&nbsp;4&nbsp;4&nbsp;4landingpage4registration_wall4logged_in5&nbsp;5landingpage6&nbsp;6landingpage6registration_wall6landingpage7&nbsp;7&nbsp;7landingpage7logged_in7payment_details_provided7purchase_submitted&nbsp;I should plot a funeel chart with these columns steps Vs count of user_id for which expected output should beStepsCount of user_idlandingpage6registration_wall4logged_in3payment_details_provided2purchase_submitted2&nbsp;But the output I am getting is&nbsp;landingpage7registration_wall4logged_in4payment_details_provided3purchase_submitted3&nbsp;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}

v-janeyg-msft · Answer

Hi,&nbsp;Anonymous&nbsp;
&nbsp;
According to your description, I create a sample.
You need to create a index column in PQ first.

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
            &amp;&amp; [user_uuid] = EARLIER ( 'Table'[user_uuid] )
    )
VAR tableb =
    FILTER (
        'Table',
        [Index]
            = EARLIER ( 'Table'[Index] ) + 1
            &amp;&amp; [user_uuid] = EARLIER ( 'Table'[user_uuid] )
    )
VAR b1 =
    MAXX ( tablea, [step] )
VAR b2 =
    MAXX ( tableb, [step] )
VAR b = [step]
RETURN
    IF (
        b = a1
            &amp;&amp; b1 = BLANK (),
        1,
        IF (
            b = a2
                &amp;&amp; b1 = a1,
            1,
            IF (
                b = a3
                    &amp;&amp; b1 = a2,
                1,
                IF ( b = a4 &amp;&amp; b1 = a3, 1, IF ( b = a5 &amp;&amp; b1 = a4, 1 ) )
            )
        )
    )
Column 2 = 
VAR a =
    MINX (
        FILTER (
            'Table',
            [step] &lt;&gt; BLANK ()
                &amp;&amp; [Column] = BLANK ()
                &amp;&amp; [user_uuid] = EARLIER ( 'Table'[user_uuid] )
        ),
        [Index]
    )
RETURN
    IF ( a = BLANK (), [Column], IF ( [Index] &gt; a, BLANK (), [Column] ) )

Then count column2.

Did I answer your question ? Please mark my reply as solution. Thank you very much.If not, please feel free to ask me.
&nbsp;
Best Regards,Community Support Team _ Janey
&nbsp;

Forum Discussion

Funnel with Array data set in Power BI

5 Replies

Related content

funnel chart

Power automate append to array function

Funnel Comparision

Funnel Chart Sorting

array to filter

user_uuid	step
1
1	landingpage	counted
1	registration_wall	counted
1	logged_in	counted
1	payment_details_provided	counted
1	purchase_submitted	counted
2
2
2	landingpage	counted
2	registration_wall	counted
2	logged_in	counted
2	payment_details_provided	counted
2	purchase_submitted	counted
3
4
4
4	landingpage	counted
4	registration_wall	counted
4	logged_in	counted
5
5	landingpage	(cannot be counted)
6
6	landingpage	counted
6	registration_wall	counted
6	landingpage	(cannot be counted as logged_in should be in this step)
7
7
7	landingpage	counted
7	logged_in	(cannot be counted as registration_wall should be in this step)
7	payment_details_provided	(cannot be counted)
7	purchase_submitted	counted

Funnel steps	Count User_id
landingpage	6
registration_wall	4
logged_in	3
payment_details_provided	2
purchase_submitted	2