Proper Modeling for Cost Table

[MostlySunny]

I'm looking for opinions on the proper way to handle my Cost Table but am struggling with what i'm finding online... I've thought of a few solutions but am looking for suggestions on the optimal path. I've given a quick rundown of options I've thought about but there's a good chance i'm going about this the wrong way, so please feel free to put out other options...

Do any of these look like the right approach? What would you do?

Quick mockup of what the basica data looks like -

Option 1 - Connect on MatRegion Key for a many-many relationship. 

• Would have to calculate Cost in a measure that pulls cost when Start Year Month <= Date Approved < End Year Month. 
• Seems logical but has been really slow in practice... to the point of a table crashing due to lack of memory

Option 2 - In Power Query, Iterate the date ranges in the cost table to give repetitive costs for each product for each day in between the date ranges via  ={Number.From([Valid From])..Number.From([Valid To])}.

• Lets me create a key [material][region][date] at the day level to use in a relationship to the fact table key [material][region][date approved] for a 1->* relationship with the many side being the fact table.
• Downside to this is that when you create a record for every day between the date ranges in the cost table you take in a ton of redundant data. If a materials cost was valid for a year, you now have 365 rows representing that cost.
  • To demonstrate, this take the cost table from 650k rows to around 70,000,000... this makes refreshing a nightmare, if it will even load

Option 3 - Same as 2, iterate date ranges in Power Query, but instead of loading to the model create the keys in PQ and merge so that only the needed costs are pulled into the reports.

• I think this is the best yet. It gets rid of having two fact tables and its a big reduction to the model size.
• Downside is that I also need my cost data for a POS table i'm required to bring in...
• This option is what feels correct to me but once I bring in the POS table and merge cost into that too, the report has begun hanging in Load window after clicking Close & Apply in Power Query. 

*** There are some other tables in the model with additional merges but the Cost Table is by far the bulk of the data ***

[BA_Pete]

Hi @MostlySunny ,

It looks as though your Cost table is a Slowly-Changing Dimension (SCD) table i.e. item rows get duplicated based on changing relevant dates, such as 'active from <date>, active to <date>'.

If so, then I'd recommend handling this in DAX. I generally recommend against using calculated columns in DAX but this is the one exception I make - I haven't found a faster or simpler method yet.

--Assumptions--

[Date Approved] sits in your Quote Line table and is a proper date.

[Start Year Month] is a proper date.

[End Year month] is a proper date.

CostTable[Material] and QuoteLine[Material] contain relatable values.

--Process--

Remove the relationship between CostTable and QuoteLine.

Add a new DAX calculated column in your QuoteLine table as follows:

..materialCost = 
CALCULATE(
    VAR __Material = VALUES(QuoteLine[Material])
    VAR __AppDate = VALUES(QuoteLine[Date Approved])
    RETURN
    MAXX(
        FILTER(
            costTable,
            costTable[Material] = __Material
            && costTable[Start Year Month] <= __AppDate
            && costTable[End Year Month] >= __AppDate
        ),
        costTable[Cost (LC)]
    )
)

You should find this to be far more performant than Many:Many relationships and Power Query gymnastics.

Pete