Strange SUMX Interaction With Date Dimension

[Anonymous]

Hello,

I have a measure that uses SUMX which works perfectly until I introduce the date column from a date dimension. Rows for the current month return the correct value whereas rows for previous months return blank values. When adding the date key from the fact table, all rows are calculated correctly until the date field from the dimension table is added. There is no date logic applied to the SUMX measure, no filtering, or any date interaction outside of the relational model.

The Model

I have a fact table for internal orders that contains Date, Product, Quantity

Product and Date Dimensions are joined on a 1-to-many relationship as to the fact table as expected

The Product table is also attached to a BOM Table via a bi-directional one-to-many relationship. The BOM table contains columns for the product, it's components, the component to product ratio, and some component attributes.

Relational Model for relevant tables

I need to build a table that shows the internal order quantity by component by date. This means looking into the BOM for the product against the internal order and multiplying the order quantity by the ratio. 

I am using these Measures:

ParentQuantity = SUM('4.1 Fact Internal Orderlines'[QuantityToBuild])

ComponentQuantity =

CALCULATE(SUMX('Dim Build BOM', 'Dim Build BOM'[RatioMultiplier]*[ParentQuantity]))

I have tried this measure with and without the CALCULATE wrapper, haven't noticed a difference in results

Creating 2 tables using these measures demonstrates the problem:

SUMX Problem - When date is added, values turn to blank

As you can see, there are the same number of rows in each table. The DateKey is taken from the internal orders fact table.

When the date value from the date dimension is added, lines in Sept 2021 (current month is Oct 2021) turn to blank for the component measure that uses SUMX()

I must stress that there is no time intelligence acting in any of the measures, or in any filter ontext in the report/page where the visual sites.

I gave one component as an example here but the pattern repeats. Consistently, Sept 2021 values are blank when the date is added.

If I switch the table focus of the SUMX to be the Internal Orders table, this problem doesn't happen, so I suspect it is a relationship issue but I have no idea why it is behaving this way. I also need to have the Build BOM table be the SUMX focus for my measure to work in the intended way.

Any answers that explain why this is happening or proposed solutions will be greatly appreciated.

Thanks

[Anonymous · Accepted solution]

ok.. 2 seconds after posting this I realised the problem. It was the many-to-many relationship on the date table to another fact table (which has year/month granularity). Putting the relationship inactive fixed the blank measure values.