The pivot table you built with "add data to data model" is not the same tool you've used for years, and that mismatch is now costing you time. Excel's Data Model uses a different query engine, OLAP instead of the classic PivotCache, and the GETPIVOTDATA syntax changes accordingly. Your formula is failing because you are trying to write a cube-referenced lookup as if it were a standard pivot reference. That approach will never work for drag-and-drop dynamic references.
Here is the practical reality: OLAP-based pivot tables require you to reference members by their full path, including the cube hierarchy. Your example formula shows you have already discovered this, but the static cell references, `X$8` and `$W9`, are being inserted as literal strings inside the member path. The OLAP engine interprets `"[Year].&[X$8]"` as a search for a year named literally "X$8," not the value in that cell. To make it dynamic, you must concatenate the cell reference outside the quoted member string. For your use case, the corrected formula would look something like: `=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Sum of Value]", "[PowerQuery_ALL_DATA_LONG_FORMAT].[Data Type].&[Main Data]", "[PowerQuery_ALL_DATA_LONG_FORMAT].[Year].&["&X$8&"]", "[PowerQuery_ALL_DATA_LONG_FORMAT].[Status].&["&$W9&"]")`. Notice the ampersands breaking the string apart so that `X$8` and `$W9` are evaluated as cell references, not text.
This is not a bug or a limitation you must tolerate. It is a direct consequence of choosing the Data Model for reliability on 66,000 rows. You made the right call for accuracy, but you now need a different function. CUBEVALUE is the correct tool for dynamic lookups in OLAP pivots, and it supports the drag behavior you want once you fix the syntax. Test the formula above on one cell, then drag it across your reference table. If the values do not update, check that your axis fields are spelled exactly as they appear in the pivot field list, including spaces and parentheses. The Data Model is strict about naming. Solve that one detail, and your PowerPoint charts will update without manual intervention.