Funnel with Array data set in Power BI

[neelofarshama]

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_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

Expected output:

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

Thanks in Advance,

Neelofar.

[v-janeyg-msft]

Hi, @neelofarshama 

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
            && [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.

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]

Hi, @neelofarshama 

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

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

Best Regards,
Community Support Team _ Janey

[neelofarshama]

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.

[v-janeyg-msft]

@neelofarshama 

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

[neelofarshama]

Hi,

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

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

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

Steps	Count of user_id
landingpage	6
registration_wall	4
logged_in	3
payment_details_provided	2
purchase_submitted	2

But the output I am getting is 

landingpage	7
registration_wall	4
logged_in	4
payment_details_provided	3
purchase_submitted	3

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}