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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Monthly Users Count for Total Users and New Users from Other Months

Hello, 

 

I am looking at data of users who logged into a website on a month-to-month basis. I have the following columns:

Name, month #, Month Name and Count. The name is a string, month # a numeric value for the month (Jan-1,Feb-2 etc.), Month name a string (January, February etc.) and count is always 1 for each row.

 

If user logged in 10 times in January it will only be recorded once for January for that user, but if they login in February as well, that will be recorded as 1 for the month of February ( even if they end up logging in again 25 more times for example).

 

The data looks like this:

NameMonth #Month NameCount
Person A1January1
Person B1January1
Person C1January1
Person D1January1
Person E2February1
Person F2February1
Person A2February1
Person B2February1
Person C2February1
Person A3March1
Person B3March1
Person E3March1
Person G3March1
Person H3March1

 

I want to create a bar graph that shows the distinct users for each month with a slider that allows you to see new users for a time interval as well. Here is an example of what the data should look like based on the above table:

 JanuaryFebruaryMarch
Total Users455
Distinct Users422
Distinct Users(A,B,C,D)(E,F)(G,H)
    
 Jan-MarJan-FebFeb-March
Total Users14910
Distinct Users866
Distinct Users(A,B,C,D,E,F,G,H)(A,B,C,D,E,F)(A,B,C,E,F,G,H)

 

I thought of creating measures, but this query has relationships to others and I need the data to respond to all visuals. Let me know if you have any suggestions.

 

Thank you.

2 ACCEPTED SOLUTIONS
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

You can create two calculate columns using dax below:

Total Users = CALCULATE(COUNT('Table'[Name]), ALLEXCEPT('Table', 'Table'[Month Name]))

Distinct Users = 
VAR Current_Month = 'Table'[Month #]
VAR Previous_Name = DISTINCT(SELECTCOLUMNS(FILTER('Table', 'Table'[Month #] < Current_Month), "Name", 'Table'[Name]))
RETURN
CALCULATE(DISTINCTCOUNT('Table'[Name]), FILTER('Table', 'Table'[Month #] = Current_Month && NOT('Table'[Name] in Previous_Name)))

Capture.PNG 

 

Community Support Team _ Jimmy Tao

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

View solution in original post

Anonymous
Not applicable

What I ended up doing was duplicating the query with the data and using the "Group By" feature in the Power Query Editor for the minimum month number to see the lowest number of the months for a user and that way I am able to tell when a user first logged in (new users by month).

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

You can create two calculate columns using dax below:

Total Users = CALCULATE(COUNT('Table'[Name]), ALLEXCEPT('Table', 'Table'[Month Name]))

Distinct Users = 
VAR Current_Month = 'Table'[Month #]
VAR Previous_Name = DISTINCT(SELECTCOLUMNS(FILTER('Table', 'Table'[Month #] < Current_Month), "Name", 'Table'[Name]))
RETURN
CALCULATE(DISTINCTCOUNT('Table'[Name]), FILTER('Table', 'Table'[Month #] = Current_Month && NOT('Table'[Name] in Previous_Name)))

Capture.PNG 

 

Community Support Team _ Jimmy Tao

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

Anonymous
Not applicable

What I ended up doing was duplicating the query with the data and using the "Group By" feature in the Power Query Editor for the minimum month number to see the lowest number of the months for a user and that way I am able to tell when a user first logged in (new users by month).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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