I have been working the latest days with a new logic that finally has worked, but also increased the model size by 50% ¡!
I know there is a better way to do it, but it requires a complex dax formula that I do not kow how to handle. As I have learnt, I have divided everything in sequential steps.
Context: We have defined different customer segments, so depending what products the customer chooses, the order is assignated to one segment or another. We have segments such us ‘Prefer rice’ or ‘Prefer chicken’, so we can understand and develop each customer segment separately.
The fact table ‘Sales table’ has one row for each order line.
|Order ID||Qty||Product||Total Order Line||Total Order|
First Step. I have created a virtual table called ‘Orders summarized and ranked’ that summarize each order by the segment related. I’ve also created a new column that ranks the segment by its sales importance.
Second Step. I have created a virtual table ‘Orders with Top 1 segment‘ that captures from the previous table the Order ID and the segment in the number 1 position
Third Step. Finally I have created a new column in Sales Table with the Segment ID.
Sales DNA Forum.pbix (1.8 MB) Segment id = related(‘Orders with Top 1 segment’[Segmento])
I know I should have used TOPN instead of Rankx but it didn’t work. Besides, the main problem is the model size, I guess the virtual tables are not so ‘virtual’
I know it can be done through a dax formula so the logic is only applied for the filter context (dates and stores) and not for the whole sales table.
Thanks in advance for your help.