Cumulative Total Works until "Fixing" Data Model then it Breaks?

[lbscolinc]

Hoping to get some guidance on this as I've spent a number of hours trying to figure out what is going wrong. I have a data model which is close to star schema but not purely one. I have two cumulative total functions which were working perfectly until just the other day. They calculate YTD running total for what we call "Switch Customers" - one metric for plan and one for actual. The functions are as below:

# of Switch Customers Cumulative Plan CY YTD = 
    CALCULATE ( [# of Switch Customers Plan]
        , FILTER ( ALLSELECTED ( fact_plan ) // calculate the cumulative total from the entire table
            , AND ( fact_plan[Fiscal Period] <= MAX ( dim_order[Date] ) // set upper bound as last available order date
                , YEAR ( fact_plan[Fiscal Period] ) = YEAR ( MAX ( dim_order[Date] ) ) // show only orders from the latest year
            )
        )
    )

# of Switch Customers Cumulative Actual CY YTD = 
    CALCULATE ( [# of Switch Customers]
        , FILTER ( ALLSELECTED ( fact_order ) // calculate the cumulative total from the entire table
            , AND ( fact_order[Key-Date] <= MAX ( fact_order[Key-Date] ) // set upper bound as last available order date
                , YEAR ( fact_order[Key-Date] ) = YEAR ( MAX ( fact_order[Key-Date] ) ) // show only orders from the latest year
            )
        ), DATESYTD ( dim_order[Date] ) // when joining with other tables that have future dates (i.e. plan numbers) don't show future dates in visualization
    )

This displays properly:

The problem I found is that our dim_order table had a many-to-many relationship with the fact_order table. This was due to an incorrect row making it through the transform layer (essentially a row without an order number / so a blank value which caused the model to see this as having "many").

We removed this row in power query, and now the data model is a proper 1-to-many.

However, this has completely broken the cumulative formulas.

We've compared both versions of the workbook and literally the only difference is that the dim_order table has one less row than before. Yet somehow now the cumulative functions don't work at all.

Any thoughts/help would be much appreciated.

[Anonymous]

Well, I'm not surprised.

Many-to-Many relationships are weak. 1-Many are strong. And what you are doing - and this is Bad Practice and bad idea in general - is you're putting as a filter in your measures the whole EXPANDED fact table. You should NEVER do it for too many reasons to state here. Filtering a table when filtering just a column is enough will always get you into troubles. ALWAYS.

Please hide all your fact tables, first of all. Secondly, slice only through well thought-out dimensions. Thirdly... just re-write the measures to adhere to Best Practices and it'll all work OK.

fact_plan and fact_order are your fact tables. You should not touch them in your code when you do filtering. Filtering must always be done by dimensions. Please, stick to this and you'll save yourself grief, frustration and time.

Best
D