Count unique values in other table

[FrederikB]

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.

[serpiva64 · Accepted solution]

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 !

[FrederikB]

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.

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

[serpiva64 · Accepted solution]

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 !