Amplitude Analytics to Power Bi - Write DAX Formula to count number of Users with certain events

[iasma]

Hello all,

First of all, thank you. the courses were very informative.
I am new to the forum and also to power BI.
The data model I have is different so I am straggling here.

I am trying to shift all analytics from Amplitude analytics to Power Bi. and I need help write the DAX formula to what I need.

I need to count the number of users who open registration and who finished registration.
open registration and finished registration is under Event type where I will be measuring other events as well.

Here is all the tables I have:
Fact table:

Lookup Tables:

Company Table

Device Table

OS Table

Browser Table

Event Table

User Table

Sample.pbix (437.7 KB)

Thank you all.

[EnterpriseDNA]

Hi @iasma, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

• Use the forum search to discover if your query has been asked before by another member.
• When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
  
• Use the proper category that best describes your topic
• Provide as much context to a question as possible.
• Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

[Heather]

a sample of your data in a PBIX file is needed to provide any significant help

[iasma]

Just added the sample. Thank you.

[Harsh]

Hello @iasma,

Thank You for posting your query onto the Forum.

Well can you please help us to categorize what actually conists as “Open Registration” and “Closed Registration” under the column Event Type under Event table because there are in total 761 different events listed under the Event Type and none of them matches as “Open Registration” and “Closed Registration”.

Please provide some more details here about what you’re trying to achieve and also which are the categories that will be taken into the account.

Thanks and Warm Regards,
Harsh

[iasma]

I apologized for the confusion.
The two event is listed as:
FINISH|REGISTER
OPEN|REGISTER

Thank you!

Basically is what I am trying to do is replace Amplitude Analytics with power BI and add features like gender and age.

Here is the report from amplitude.

[Harsh]

Hello @iasma,

Thank You for the clarification of the categories. So based on the categories and conditions provided. Below are the formulas provided for the reference based on which you’ll be able to evaluate “Open Registration” and “Close Registration” -

Open Registration = 
CALCULATE( COUNTROWS( 'Fact web Analytics' ) ,
    FILTER( Event , 
        Event[Event Type] = "OPEN|REGISTER" ) )


Close Registration = 
CALCULATE( COUNTROWS( 'Fact web Analytics' ) ,
    FILTER( Event , 
        Event[Event Type] = "FINISH|REGISTER" ) )

Also attaching the working of the PBIX file for the reference.

Thanks and Warm Regards,
Harsh

Sample - Harsh.pbix (449.7 KB)

[iasma]

Thanks a lot for the help.
How can I show with the date?

I updated my response above with the report in amplitude that I am trying to duplicate.

[Harsh]

Hello @iasma,

Since the results against the given categories are not enough it looks very plain. But this is how you can show it if you’ve more diversified data. Below is the screenshot as well as PBIX file provided for the reference -

Hoping this helps you.

Also if you’re looking for some visualization ideas you can check out the education portal as well. Below are links provided for it as well.

Thanks and Warm Regards,
Harsh

Sample - Harsh.pbix (480.2 KB)

Dashboarding & Data Visualization Intensive

Data Visualization Tips

Redirection to EDNA Showcase

Power BI Challenge Showcase

[iasma]

I appreciate all the help @Harsh

I still don’t understand why its not showing any numbers for the registration, I am using the same data in amplitude. I don’t know if I need to do different calculation including the users or if my data model set right.

[Harsh]

Hello @iasma,

As per the conditions specified in the post, the Event ID for “OPEN|REGISTER” is 155 and for that only 1 row is available under the Web Analytics Table and for “FINISH|REGISTER” the Event ID is 19 against which only 14 rows are available under the Web Analytics Table. Below are the screenshots provided for the reference in order to to cross - check the results with the derived formula -

So as per the data provided to me the data are correct. I would request you to check the data internally with the team.

Hoping cross - checking of results is helpful to you.

Thanks and Warm Regards,
Harsh

[iasma]

Thanks a lot Harsh,

I figured why the data was not showing.
In the data table, the date was included with time. so I created a separate column for the date and it worked.

[Harsh]

Hello @iasma,

You’re Welcome.

Well if you’ve checked the file which I provided above in that I also did the same thing. Below is the screenshot provided for the reference -

I’m glad that I was able to assist you and you found the solution helpful.

Thanks and Warm Regards,
Harsh