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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Riddlex17
Employee
Employee

Creating a funnel chart from a derived table

Hi Folks,

I'm trying to build a funnel chart, but the requirement is bit specific. Let me explain the requirement. 

 

Following are the different Funnels steps:

Step 1: "Access Step": It's the first step to check whether the user has the access to the portal.

Step 2:  "Check New User Step": This step checks how many new users have joined the portal. However, the new users will come from the first step i.e. 2nd step becomes the subset of the first step.

Step 3: "Home Page Interaction": The 3rd step is see how many new users interacted with the home page. Again, the users in  this step should come from 2nd step.

 

I can get the distinct user count for the first step using the following DAX statement.

 

Access Step = CALCULATE(DISTINCTCOUNT('Bookings Funnel Data_RAW'[UserId]), FILTER('Bookings Funnel Data_RAW','Bookings Funnel Data_RAW'[EventName] IN {"HasPersonalAccessNotSharedAccess","SharedAndPersonalBookingsAccessible",
                "HasSharedAccessNotPersonalAccess"}))
 
However, for the 2nd step, I created a derived table from Step 1, but the problem is, I lose all the other event_names required to filter the users for step 2 and Step 3. Ideally, the derived table should contain the user_ids from step 1 and the event_names required for step 2.
 
Derived table created by me (It's wrong):
New Users table = CALCULATETABLE('Bookings Funnel Data_RAW',FILTER('Bookings Funnel Data_RAW','Bookings Funnel Data_RAW'[EventName] IN {"HasPersonalAccessNotSharedAccess", "SharedAndPersonalBookingsAccessible", "HasSharedAccessNotPersonalAccess"}))
 
I have solved this problem in SQL, but the client wants to see the funnel chart in Power BI.
 
SELECT COUNT(distinct UserId) as user_count
FROM tenants_data
WHERE EventName IN('HasPersonalAccessNotSharedAccess',
                             'SharedAndPersonalBookingsAccessible',
                             'HasSharedAccessNotPersonalAccess')
 
UNION

SELECT COUNT(distinct UserId) as user_count
FROM tenants_data
WHERE EventName IN ('CreateBookingPageTemplateShown','CreateNewMeetingTypeTemplateShown')
AND UserId IN (SELECT distinct UserId
                         FROM tenants_data
                         WHERE EventName IN('HasPersonalAccessNotSharedAccess',
                                                     'SharedAndPersonalBookingsAccessible',
                                                     'HasSharedAccessNotPersonalAccess')
                        )
 
Any solution would be highly appreciated. A sample snapshot of base data is attached.sample data.png
2 REPLIES 2
Riddlex17
Employee
Employee

Check New User Step = CALCULATE(
    DISTINCTCOUNT('Bookings Funnel Data_RAW'[UserId]),
    FILTER(
        'Bookings Funnel Data_RAW',
        'Bookings Funnel Data_RAW'[EventName] = "NewUserEvent" && // Replace "NewUserEvent" with the actual event name for new users
        'Bookings Funnel Data_RAW'[UserId] IN VALUES('Bookings Funnel Data_RAW'[UserId])
    )
)

This solution worked for the 2nd Step. Thanks!

v-jialongy-msft
Community Support
Community Support

Hi @Riddlex17 

 

The following DAX may help you solve your problem:

 

Step 1: Access Step

You’ve already defined this measure:

Access Step = CALCULATE(
    DISTINCTCOUNT('Bookings Funnel Data_RAW'[UserId]),
    FILTER(
        'Bookings Funnel Data_RAW',
        'Bookings Funnel Data_RAW'[EventName] IN {
            "HasPersonalAccessNotSharedAccess",
            "SharedAndPersonalBookingsAccessible",
            "HasSharedAccessNotPersonalAccess"
        }
    )
)

 

Step 2: Check New User Step:

Check New User Step = CALCULATE(
    DISTINCTCOUNT('Bookings Funnel Data_RAW'[UserId]),
    FILTER(
        'Bookings Funnel Data_RAW',
        'Bookings Funnel Data_RAW'[EventName] = "NewUserEvent" && // Replace "NewUserEvent" with the actual event name for new users
        'Bookings Funnel Data_RAW'[UserId] IN VALUES('Bookings Funnel Data_RAW'[UserId])
    ),
    'Bookings Funnel Data_RAW'[EventName] IN {
        "HasPersonalAccessNotSharedAccess",
        "SharedAndPersonalBookingsAccessible",
        "HasSharedAccessNotPersonalAccess"
    }
)

 

Step 3: Home Page Interaction:

Home Page Interaction = CALCULATE(
    DISTINCTCOUNT('Bookings Funnel Data_RAW'[UserId]),
    FILTER(
        'Bookings Funnel Data_RAW',
        'Bookings Funnel Data_RAW'[EventName] = "HomePageInteractionEvent" && // Replace with the actual event name for home page interactions
        'Bookings Funnel Data_RAW'[UserId] IN VALUES('Bookings Funnel Data_RAW'[UserId])
    ),
    'Bookings Funnel Data_RAW'[EventName] = "NewUserEvent" // Assuming continuity from the New User step
)

 

 

 

 

 

 

 

Best Regards,

Jayleny

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors