Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Power BI community,
Despite lots of intensive reading and searching, I'm stuck trying to generate two KPIs from some of my data.
I have two tables:
Users
userID | userName |
111 | John |
222 | Sarah |
333 | Bill |
444 | Jane |
555 | Alex |
Visits
userID | Month | NumberOfVisits |
111 | Jan | 7 |
111 | Feb | 20 |
111 | Mar | 6 |
222 | Feb | 2 |
333 | Jan | 6 |
333 | Feb | 66 |
444 | Feb | 2 |
444 | Mar | 17 |
444 | Apr | 10 |
There's a one-to-many relationship from the userID on the Users table to the userID on the Visits table. Not every user has an entry in the visits table though.
The two metrics I'm trying to achieve are how many unique users visited the website with certain visit counts like between 1 and 5 in sum across all months, and how many did not visit the website (ie they are in the User table but not in the Visits table).
To complicate it further, I'm trying to make both of those metrics respect a slicer on the Month so that they update for whatever month(s) the report viewer has selected.
I've stared at this all day and I'm so confused! 😕
Solved! Go to Solution.
Hi @RichardP,
You are welcome. Try this calculate field formula
=COUNTROWS(FILTER(SUMMARIZE(Users,Users[userID],"ABCD",SUM(Visits[NumberOfVisits])),[ABCD]>=1&&[ABCD]<=6))
The download link for the revised file is the same as shared above.
Hope this helps.
Hi,
You may refer to my solution in this file.
Hope this helps.
Thank you so much for this, it is brilliant and a huge help.
I think there is just one thing not quite as I would like it in the metrics.
I'm splitting the metrics into 1-5 visits, 6-10 visits aind 11+ visits.
Doing this, I'm finding that if a user has a month with 1 visit and another month with 6 visits then if both months are selected in the slicer the user is counted in both metrics.
I think to avoid this I'd like to sum all of their visits for the selected month(s) and then count them.
Is there a way to add that into the calculations somewhere?
Thank you once again!
Richard
Hi @RichardP,
You are welcome. Try this calculate field formula
=COUNTROWS(FILTER(SUMMARIZE(Users,Users[userID],"ABCD",SUM(Visits[NumberOfVisits])),[ABCD]>=1&&[ABCD]<=6))
The download link for the revised file is the same as shared above.
Hope this helps.
Amazing, thank you so much! 🙂
You are welcome.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |