Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RichardP
Helper I
Helper I

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

You may refer to my solution in this file.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Amazing, thank you so much! 🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.