Forum Discussion
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:
| id | Date | personId |
| 1 | 01-03-2022 | 1 |
| 2 | 02-03-2022 | 1 |
| 3 | 03-03-2022 | 2 |
| 4 | 04-03-2022 | 3 |
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.
Hi,
you have confused the two Table.
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 !
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] )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.
3 Replies
Hi,
you have confused the two Table.
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 !
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] )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.
