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.
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.
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
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
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.
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} |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |