Hello Community - I've been asked to group our returns based on any return that has a power cord on any of the return lines. For example: There are 4 distinct Return #'s below. But only 3 of them have a power cord as part of the return, and that are then part of the Family = cords.
In this example, the expected result would be 3 Return #'s, because there are three distinct Returns that have "family" = cords. (the logic is that if any of the lines are for a power cord item, per Return #, than I need to count the Return #. If not, don't count it.
In my mind, the process involves grouping the Return #'s, and if any of those Return #'s include an item that is a power cord (i.e. part of the cord "family") then count the number of distinct Return #'s.
I was thinking using CountAX and creating some type of virtual table might work, but having no luck so far.
We have a Returns fact table. And a Return Item dimension table (that has the item and family code).
Return # Ret Line Item Returned Family Code
Ret-9434 1 1234 cords
Ret-9434 2 2345 switches
Ret-8732 1 3456 cords
Ret-9863 1 5634 circuits
Ret-9863 2 4589 cords
Ret-9863 3 5789 switches
Ret-7698 1 1245 bulbs
