cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RichardP Regular Visitor
Regular Visitor

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

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

userIDuserName
111John
222Sarah
333Bill
444Jane
555Alex

 

Visits

userIDMonthNumberOfVisits
111Jan7
111Feb20
111Mar6
222Feb2
333Jan6
333Feb66
444Feb2
444Mar17
444Apr10

 

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! :/

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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.

5 REPLIES 5
Super User
Super User

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

Hi,

 

You may refer to my solution in this file.

 

Hope this helps.

 

Untitled.png

RichardP Regular Visitor
Regular Visitor

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

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

 

Super User
Super User

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

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 Regular Visitor
Regular Visitor

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

Amazing, thank you so much! Smiley Happy

Super User
Super User

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

You are welcome.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 174 members 1,781 guests
Please welcome our newest community members: