Metrics based on one-to-many relationships - with slicers

[RichardP]

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! 😕

[Ashish_Mathur]

Hi,

You may refer to my solution in this file.

Hope this helps.

[RichardP]

hi @Ashish_Mathur

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

[Ashish_Mathur · Accepted 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.

[RichardP]

Amazing, thank you so much! 🙂

[Ashish_Mathur]

You are welcome.