Help with optimizing DAX measure

Hi Anonymous 

Unfortunately these don't look like particularly inefficient queries to me. (Obviously without seeing the real data).

The immediate thing that springs to mind is an aggregation table, so the query can be run on a table with much less than 200m rows.  https://docs.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced 

However, you'll immediately hit a limitation of agg tables: your 'Fact Table' would need to be in DirectQuery mode.  This article explains a workaround for that: https://dax.tips/2019/11/15/creative-aggs-part-vi-shadow-models/