Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a lookuptable called 'Users' with UserID and user name and dateActivated . I have another table ('loginactivity') that has userID and userLogins and date. They are connected via userID and a calendar table.
I want to make a graph of the count of user logins with the x-axis being date. I also want another visual with a list of UserId and UserName so when you click on the graph, it will filter to show only userIDs that logged in.
If I use the formula Calculate(count('loginactivity' [userId]), 'loginactivity' [action]="Login") to calculate the graph rows AND I have another matrix table with a list of 'Users'[userID], when I click on the graph, the matrix table doesn't filter. How should I change my formula to enable the matrix to filter?
Solved! Go to Solution.
In that case, simply add just the logins count measure to the filter on the matrix table.
Login count measure:
Logins = CALCULATE(COUNT(User_Login[UserID]), FILTER(User_Login, [Action] = "Login"))
Login measure on the matrix filter list and set to is not blank:
And a date selected on the graph that is filtering the matrix to just those users that had a login on that day:
Just to make sure I understanding correctly, you want a DISTINCT user count?
If you create a measure using the distinct count on the UserID column on the User_Login table you can actually put the AppVersion and count of users in a matrix and not require any further actions to filter.
Count of Users = CALCULATE(DISTINCTCOUNT(User_Login[UserID]), FILTER(User_Login, [Action] = "Login"))
Let me know if that answers the question.
Create a new measure that checks if UserID on the logins table is filtered, if it is, then return the count measure you created otherwise return 1. Now on the filters for the matrix visual add that newly created measure and set it to is not blank.
Also worth noting that the graph should be filtering the matrix if the graph is using any columns from the UserID table.
Table Filter = IF(ISFILTERED(User_Login[UserID]), [Logins], 1)
@RMB what you gave me did the opposite of what I was looking for, I want to be able to click the graph and filter the userID matrix. Your solution lets me filter the graph when I click on the UserId matrix. Can you please advise how I would do the opposite? I am inclined to think this is a formula issue but I am unfamiliar with what to do
What is your axis on the graph? The above is only necessary if you're graphing with an axis from the login activity table. Do you have a sample screenshot you could share?
In that case, simply add just the logins count measure to the filter on the matrix table.
Login count measure:
Logins = CALCULATE(COUNT(User_Login[UserID]), FILTER(User_Login, [Action] = "Login"))
Login measure on the matrix filter list and set to is not blank:
And a date selected on the graph that is filtering the matrix to just those users that had a login on that day:
@RMB that worked like a dream. Could I ask you about another one? Your help would be GREATLY appreciated.
I want to create another matrix that is filtered BY clicking the same exact graph of user login activity that looks like this:
AppVersion | Count of Users |
1.3 | 12 |
1.4 | 14 |
(So that when you click the bar graph, the count of users will change based on how many users logged in with that app version at that time)
I have the same 2 tables:
Logins - but this time, there is a new column with an app version id with each login instance. So the table looks like this
UserID | LoginActivity | Date | AppVersion |
1 | Login | 2/12/2020 | 1.4 |
1 | Login | 2/13/2020 | 1.4 |
2 | Login | 2/12/2020 | 1.3 |
I also have a users table with a unique list of userIDs
Again, my graph login count measure is from the Logins table and is Logins = CALCULATE(COUNT(User_Login[UserID]), FILTER(User_Login, [Action] = "Login"))
But I need to use App version from the Logins table BUT UserID from the Users table in the matrix
Does this make sense?
Just to make sure I understanding correctly, you want a DISTINCT user count?
If you create a measure using the distinct count on the UserID column on the User_Login table you can actually put the AppVersion and count of users in a matrix and not require any further actions to filter.
Count of Users = CALCULATE(DISTINCTCOUNT(User_Login[UserID]), FILTER(User_Login, [Action] = "Login"))
Let me know if that answers the question.
For the first one, you should just be able to use something like COUNTROWS(RELATEDTABLE(...
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |