Tabular DAX Daily Many To Many Association

I am almost at the end of my tether on a problem that i have in a model currently... basically, i have a 'temporal' many to many mapping table which maps commission rates for managers over time... these can change daily, (but do so rarely), so I've tried to avoid just having a huge table with the same values repeated albeit for different specific dates, if i do this anyway i end up with a 200 million record table, crucially though more than one manager can get commission for the sale of a certain product type :s

Note: Some commissions only go to a single manager, and some go to multiple, and this can switch over time..

What I've done instead is hold ValidFrom, and ValidTo dates in the mapping table...

Every solution i come up with is deathly slow, and i just have no idea if there even IS a solution at this point... here is a link to a very small sample:- http://1drv.ms/1gOr7uw

The area that i think is the most troublesome is actually getting the correct "rate" for a given Manager, on a given day... the only way i can seem to be able to do this is nested SUMX, but there must be something slicker that I'm missing?!

One thing i thought about (but failed to actually implement) was just to hold an effective date, and filter using that and leverage LASTNONBLANK() or something?

Perhaps someone with some fresh eyes can help me out? Pulling my hair out here!

Edit: Someone will probably say why don't i do this in the ETL, what i've not shown here is that I have other measures that don't need to be split by the managers, but instead the full amount should be reported for each... but the total not a sum of all the managers (aka, default M2M behaviour)

Perhaps I need two fact tables? Perhaps someone could model my data in Excel to achieve a 'split' of the Facts, as i see it, whichever way the problem is cut some calc is going to need to be done at run-time. I think?!

Ty.

Solution

Give this a try, I have not checked performance, but I don't expect them to be super... nevertheless, at least it looks elegant :)

Amount := 
SUMX (
    SUMMARIZE (
        Mapping,
        Mapping[Manager],
        Mapping[Rate],
        Mapping[ValidFrom],
        Mapping[ValidTo],
        Products[Products],
        "SalesInPeriod", Mapping[Rate]
            * CALCULATE (
                SUM ( Sales[Amount] ),
                VALUES ( 'Date'[Date] ),
                DATESBETWEEN (
                    'Date'[Date],
                    Mapping[ValidFrom],
                    Mapping[ValidTo]
                )
            )
    ),
    [SalesInPeriod]
)

If you want to count, still keeping the temporal M2M working, this should do the trick:

Count := 
CALCULATE (
    COUNTROWS ( Sales ),
    GENERATE (
        SUMMARIZE (
            Mapping,
            Mapping[ValidFrom],
            Mapping[ValidTo],
            Products[Products]
        ),
        DATESBETWEEN (
            'Date'[Date],
             Mapping[ValidFrom],
             Mapping[ValidTo]
        )   
    ),
    VALUES ( 'Date'[Date] )
)

This latter version is easier, because it does not need the Rate and Manager (or, it needs the manager, but it is already in the filter context and no SUMX is required since you are aggregating using canonical M2M)