Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Name | Month # | Month Name | Count |
Person A | 1 | January | 1 |
Person B | 1 | January | 1 |
Person C | 1 | January | 1 |
Person D | 1 | January | 1 |
Person E | 2 | February | 1 |
Person F | 2 | February | 1 |
Person A | 2 | February | 1 |
Person B | 2 | February | 1 |
Person C | 2 | February | 1 |
Person A | 3 | March | 1 |
Person B | 3 | March | 1 |
Person E | 3 | March | 1 |
Person G | 3 | March | 1 |
Person H | 3 | March | 1 |
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:
January | February | March | |
Total Users | 4 | 5 | 5 |
Distinct Users | 4 | 2 | 2 |
Distinct Users | (A,B,C,D) | (E,F) | (G,H) |
Jan-Mar | Jan-Feb | Feb-March | |
Total Users | 14 | 9 | 10 |
Distinct Users | 8 | 6 | 6 |
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.
Solved! Go to Solution.
@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)))
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.
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).
@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)))
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.
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).
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |