Hello all !
I found a solution to my problem but I think it's not really robust and quick dirty so I would like your opinion and maybe another and better solution.
For context, we have Sales fact (called Transaction items) which are linked to some dimension. One of them is a Client Action that can be an Event, a Gift, a Task or a Note in our CRM.
An Action is linked to a Client but we want to link them to a Transaction item using the Client and the dates of both the Transaction item and the Action.
To link just one Action to a Transaction item we use the following rules :
1. An Action of type Event or Gift can be linked to a Transaction item if it happened up to 14 days before the Transaction item
2. An Action of type Note or Task can be linked to a Transaction item if it happened up to 8 days before the Transaction item
3. If both Event/Gift and Note/Task are linked to the TI, it's the Gift/Event which should be linked
4. If more than one Action is linked, we finally link the latest one
Here's the steps I did :
- I added a Select Order column to the Action Client table, where Event/Gift = 10 and Note/Task = 20
- Create a mapping table to get each Actions for each Client
- In Transaction Item
- I create a Custom Column using Table.SelectRows to apply link rules 1 and 2 (so join based on the Action type and the dates)
- Once this table is expanded I sort the rows by Transaction Item (Ascending), Select Order (Ascending) and Action date (Descending)
- I add an incremented Index column in the table
- I group by each key of the fact and use All rows grouping algonside Table.Min on the incremented index to get the expected row
This is working but as you might guess I find that relying on a Sort of the rows to apply rule 3 and 4 is quite meh. Unfortunately I don't find an other way, my lack of Power Query knowledge shines up here !
So if you think of a better solution I would be very happy to hear it 🙂
I've uploaded my pbix and the Excel example I build to illustrate this : https://drive.google.com/drive/folders/16cTRD8hpmelYueyvnU7HMSyNq7Bq-vDo?usp=sharing
