At the moment, I have my data table, my date table and my time table. It lists the transactions per product and if there are multiple products per transaction they share the same transaction ID.
As I'm typing this, it feels like a two parter. The first part; I'd like to be able to work out from what has been ordered if on the order it is one or more people based upon conditions. The conditions for example being if on one unique transaction ID Order, there are two meals, or two coffee's ordered then that means there are two people in that group and therefore a group of two.
My first thoughts is to either group the data or create a conditional column to identify if more than for example one meal has been ordered per transaction ID or more than one coffee. And then COUNT or SUM the resultant to identify the group size; but I'm really not sure, I've tried it a few times but I feel I'll need to use a calculated column given the number of conditions but I'm not sure.
The second part would be then group people who use the same table in the Notes Column and classify them as group (they only recieve a unique transaction ID if they pay together/on the same card/put all their orders on that one docket and pay it in one clean transaction). So for example; if there are 3 transactions ID's all within a 5minute period (that's the part that has me lost, how to create that strict window as so if numbers are used again they aren't recounted) which share the same "25 Inside" Note; how to classify them as a "Group of 4" or a "Group of 3".
I realise this is quite messy and tough but any direction or help would be greatly appreciated.
Here's my PBIX if that helps anyone try things; My Pbix Link
I've re-typed what I'm trying to achieve if this might help add some clarity to what I'm trying to do.
I feel there are two conditions.
The first being, if there are multiple different unique transaction IDs within a ten minute rolling period have the same value such as “25in” appear in the Notes column of the ‘itemdetailsdogfood$’ table then these orders are classified as being a part of a group of people. So if for example there were 3 Unqiue transaction IDs between 8.21am and 8.27am with the same Note “25 In” for example then that is a group of three people.
The next part is if on a Unique Transaction ID there is more than one of any item category in the [Section2] category then the subsequent numbers represent an additional person. So each group begins with one person. If for example there are two “barista” values which appear within one Unique Transaction ID then that represents a group of two people.
From those two sets of conditions I would like to be able to build a table which showed the Group Size (one person, two people, three people, etc) and the number of groups. I would also like to be able to filter this by the datetable as well as ideally using filters so I am able to filter just the groups that order something from [Section2].