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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
FrederikB
Frequent Visitor

Count unique values in other table

I have a table called bookings, and a table called persons. I want to calculate how many different persons have gotten a booking within some time interval.

Bookings:

idDatepersonId
101-03-20221
202-03-20221
303-03-20222
404-03-20223

 

Persons:

id
1
2
3
4

 

I want to know how many bookings each person has had, which I calculate by:

 

total bookings = DISTINCTCOUNT('booking'[id])

 

I can display this in the persons table, and see that it works right.

 

And I then calculate how many unique persons have gotten a booking by:

 

Persons with bookings = 
CALCULATE(
    COUNTROWS('persons)'), 
    FILTER('persons', 'persons'[total bookings] > 0)
)

 

 

When I set a slicer to march 2022, I expect to see:

  • total bookings: 4
    • personID=1, total bookings: 2
    • personID=2, total bookings: 1
    • personID=3, total bookings: 1
  • Persons with bookings: 3
    • PersonID=1,2,3

 

But on 'total bookings' is calculated proberly. And I can't seem take get 'Persons with bookings' right. At the moment it simply returns (Blank) for each of the persons rows.

2 ACCEPTED SOLUTIONS
serpiva64
Solution Sage
Solution Sage

Hi,

you have confused the two Table.

serpiva64_0-1646323052788.png

 

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

 

 

View solution in original post

Hi @FrederikB ,

You can create measures like this to get the result:

total bookings = DISTINCTCOUNT('bookings'[id])
Persons with bookings = 
VAR tab =
    SUMMARIZE (
        'bookings',
        'bookings'[personId],
        "total bookings", [total bookings]
    )
RETURN
    COUNTX ( tab, [personId] )

vyingjl_0-1646706841555.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
FrederikB
Frequent Visitor

Thank you for your reply, but I am not sure that I either understand what you mean, or that the provdided solution solves the problem.

It seems like 'Persons with bookings' is summed to four, even though only 3 unique persons have gotten a booking.

Hi @FrederikB ,

You can create measures like this to get the result:

total bookings = DISTINCTCOUNT('bookings'[id])
Persons with bookings = 
VAR tab =
    SUMMARIZE (
        'bookings',
        'bookings'[personId],
        "total bookings", [total bookings]
    )
RETURN
    COUNTX ( tab, [personId] )

vyingjl_0-1646706841555.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

serpiva64
Solution Sage
Solution Sage

Hi,

you have confused the two Table.

serpiva64_0-1646323052788.png

 

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors