Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago
    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!