The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
102 | |
82 | |
62 | |
56 |
User | Count |
---|---|
253 | |
119 | |
115 | |
95 | |
70 |