Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
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)
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)
Hi,
try this:
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |