DAX Head Scratcher

marcorusso Well, the phrase clear as mud comes to mind! 🙂

What is truly vexing to me is that both of the formulas result in the EXACT same DAX query from what I can tell yet one takes 100 times longer to return:

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('1OptionMaster'[OptionID], '1OptionMaster'[PlanID], '1Plans'[PlanID]), "IsGrandTotalRowTotal"
      ),
      "v1Frequency", '1OptionMaster'[1Frequency]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      '1OptionMaster'[OptionID],
      1,
      '1OptionMaster'[PlanID],
      1,
      '1Plans'[PlanID],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  '1OptionMaster'[OptionID],
  '1OptionMaster'[PlanID],
  '1Plans'[PlanID]

If it is the same query, then...? If nothing else, this seems to expose a deficiency in the Performance Analyzer in that it is not capturing everything that is truly going on within DAX calculations?

My understanding of the data model is that this predicament comes about thusly:

• There is option data about plans with costs, prices, GM, etc. This is the OptionMaster table and is a fact table with options and plan dimension tables (AreaOptionMaster and Plans). This provides the GM for the options but is essentially "current". As in, there is no date with a history of costs/prices/etc. The same option can be sold across many different plans.
• However, they also want to see how often those options are sold. This comes from another fact table that has the option id in it as well as an "opportunity" ID that ties it back to the original opportunity. This is a fact table of option sales over time called OptionSelection. Furthermore, this "frequency" can come in different forms, a global frequency, how many times sold period, a "local freqency", how many times sold across a particular area and other types of frequency such as how many times sold for a particular plan across all areas. This fact table has a single dimension table, QuotesContracts that has additional information about the sale and includes the Plan ID that was sold.

So, at the end of the day, there are two fact tables involved and the customer wants to see information from both of them. I don't see a way to combine the two fact tables into a single fact table as they are at extremely different granularities, one historical sales over time and the other is just a list of options, plans and their current costs and prices. Perhaps we could combine the OptionSelection and QuotesContracts tables and then form a direct relationship between the fact tables using a combined key (option id & "|" & plan id) but, please remember, this is an extremely simplified view of the data model. There are actually about 20 tables involved in the data model and at least two or three additional fact tables (there is a fact table for plan costs and material costs for example). Think of it this way, plans and options are made up of "items" (raw materials) that have their own cost and thus a plan and an option cost are essentially a "roll-up" of these material costs. There are additional tables in the model that define for an option and a plan all of the materials that make up these plans and options. Then there is a budget fact table as well that details out the budget for the work required to manufacture the particular plan and option. Then, of course, consider that different vendors may provide the materials for the option and plan at different costs so that is also in the model.

There is actually a ton more complexity to this than I am conveying here but hopefully this provides some idea. That's why I wanted to focus this on the DAX and what they heck it is doing exactly versus the data model because it's just not so simple as to "fix" the data model when it is complex and working for literally everything else except this particular calculation.