Forum Discussion
Creating a calculated column that indicates count number of another column range
Apparently I'm completely missing something very basic, I have tried all morning - even being smashed around by ChatGPT :-).
I have a table with ticket_id's for every guest at certain events hosted by certain people.
I want to end up with a column that list a 1 or 0 for each ticket id indicating whether the seller/host of this ticket has more than 2 events on that particular day.
So, I have this.
| ticket_id | guest_id | date | event_id | host_id |
| 1 | 1 | 01/01/2023 | 1 | 1 |
| 2 | 2 | 01/01/2023 | 2 | 1 |
| 3 | 3 | 01/01/2023 | 3 | 1 |
| 4 | 4 | 01/01/2023 | 1 | 1 |
| 5 | 1 | 02/01/2023 | 4 | 1 |
| 6 | 2 | 02/01/2023 | 4 | 1 |
| 7 | 1 | 03/01/2023 | 5 | 1 |
And I want to add a column like this
| ticket_id | guest_id | date | event_id | host_id | >2/day |
| 1 | 1 | 01/01/2023 | 1 | 1 | 1 |
| 2 | 2 | 01/01/2023 | 2 | 1 | 1 |
| 3 | 3 | 01/01/2023 | 3 | 1 | 1 |
| 4 | 4 | 01/01/2023 | 1 | 1 | 1 |
| 5 | 1 | 02/01/2023 | 4 | 1 | 0 |
| 6 | 2 | 02/01/2023 | 4 | 1 | 0 |
| 7 | 1 | 03/01/2023 | 5 | 1 | 0 |
Then I want a visual that can display each event id that is part of a sequence where the host has more than 2 events per day. Baiscally removing all event_id where the host has 2 or less on a given day.
That means the above would give me something like this as a visual:
| date | event_id | host_id |
| 01/01/2023 | 1 | 1 |
| 01/01/2023 | 2 | 1 |
| 01/01/2023 | 3 | 1 |
| 01/01/2023 | 1 | 1 |
It might be cool to sort out duplicate event_id's from that visual, but that's not necessary.
Thanks to everyone who can help.
Thanks, it almost worked, but I managed to tweak it a bit with a little help. This is what ended up working:
Column = if(CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table,Table[host_id],Table[date]))>2,1,0)
2 Replies
Hi,
try this:
Column = if(CALCULATE(COUNT('Table'[host_id]),ALLEXCEPT('Table','Table'[date]))>2,1,0)basically it's counting the the host id but changing the context to only count against the exact date.If I answered your question, please mark my post as solution, Appreciate your Kudos 👍Thanks, it almost worked, but I managed to tweak it a bit with a little help. This is what ended up working:
Column = if(CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table,Table[host_id],Table[date]))>2,1,0)
