It's working as expected. When you add the Secondary SUM(Sales), it's blending data based on the fields you have on your view. In this case, DAY(Order Date) (Primary).
Instead of filtering the Secondary datasource on the full date, it's aggregating by day and only keeping Day(Order Date) = 1 or 2.
The large numbers you are getting are essentially the sum of Sales that happen on the 1st or 2nd of any month, not the 2 days your are interested in.
You can see that by creating a new view with DAY(Order Date) and filtering where Day = 1 or 2.
If you want to see the results you were expecting, you have to add a specific date filter on the Secondary Order Date (Date = 1/1/2009 or 1/2/2009).
Does it make sense?
It make sense.
I can see in the worksheet from Secondary that if i removed the filter, the number corresponds to the SUM of all 1s and all 2s.
So what i missed is that the Blending is based on the fields in the view and not only on the fields in the Filters.
I am not sure i feel it coherent, it seems obvious that you want to blend on the exact same date that you filter.
I would have expected that it query Dec 1 from Secondary and then apply DAY[Order Date] in the view.
Anyway , thanks for clearing this up