Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.