Which table function is best to see the temporary sumx tables?

[haganistic]

Hi!

I'm really trying to improve my DAX and focusing on row context. I know DAX studio is great to see tables, however its not super clear to me which tables fucntions exactly replicate the temporary tables created by a nested sumx? I know there are loads of table fucntions (crossjoin, summarize, summarizecolumns, addcolumns, selectcolumsn, etc) but if I wanted to visualize step by step how nested iterators (either sumx or filter) create the temporary tables it will really help my understanding before I get into filter context and context transition. For example, if we take the following formula in the italians books using the Contoso dataset, how can I step by step (eg. start with one iterator and then add the next, etc, recreate the final temporary table of the code below?

If the sumx was nested against a filter table, would it change the answer on how to best recreate it in DAX studio?

SUMX (' Product Category ',

SUMX ( RELATEDTABLE (' Product '), 

SUMX ( RELATEDTABLE (Sales), Sales [Quantity] * ' Product '[Unit Price]* ' Product Category '[Discount])

[Anonymous]

I don't think you can re-create it step by step. Temporary tables - whatever that means (I guess you really mean the Storage Engine caches) - are temporary, volatile and only used internally. You can't visualize them anywhere but in your mind's eye. But I don't really think it's necessary to know what they look like. In DAX, it's just enough to follow the rules to the letter to know what's going on.

When you write DAX, you should 1) know the rules of calculation by heart without even thinking about them, 2) follow the usual mathematical logic and 3) know what the model looks like. This is more than enough to make you a good DAX programmer.

[haganistic]

hi! Thanks for taking the time to reply. So I would of thought for the above that cross join, would be the best function to try visually how a sumx nested table looks like? Or perhaps summarize or generate. But from your response, am I to understand that there are too many variables that would determine how that nested table would like, thus there is no pragmatic approach or rule of thumb to replicate the nested tables? Therefore need to learn the rules of wanting to understand. Is that correct?

[Anonymous]

Well, it's complex. Sometimes the table iterated over can be a full cross join, other times it can be a "full" table with auto-exists kicking in, still other times it can be a certain subset of some cross join. It really depends on the situation. Roughly you can imagine you've got a cross join of the attributes but this is only true on the conceptual level most of the time but not always. It's complex.