Hi, I’m a beginner in DAX, having 2 below questions after finishing the mastering DAX course on EnterPriseDNA:
Virtual Table : I’m seeing a lot of available functions to create “virtual tables”: ADDCOLUMNS, SUMMARIZE, SUMMARIZECOLUMNS, GROUPBY. However from what I understood their key function is to mash different Column/Table together with slightly different quirks (e.g CURRENTGROUP within GROUPBY, add additional filter/evaluation context,…).
I’m currently confused between the uses and differences between these functions as a beginner . From your experience, how do you know which situation to use which “virtual table” function/ which one of the above you use the most?
RANKX functions : I’m aware of the existences of filter context & row context. Still cannot wrap my head around why do you have to put ALL/ALLSELECTED in your ‘Table’ argument in RANKX? Simple example below, AFAIK RANKX should rank current products based on the current products filtered by the filter context, thus why do we need to wrap ALL/ALLSELECTED around the Products table else it won’t work?
RANKX Test =
RANKX(Products, [Rolling Sales 7 days], , DESC, Skip) // doesn’t work
//but wrap the Product table around ALL/ALLSELECTED works
RANKX Test =
RANKX(ALLSELECTED(Products),[Rolling Sales 7 days],DESC,Skip)
SUMMARIZE - Use it for performing grouping example SUMMARIZE ( Sales, Products[Brand], Products[Color] ), but don’t use it for adding new columns, the performance is not good, also it creates clusters to solve a query, which is an extremely complex process to identify.
ADDCOLUMNS - Use it to add new columns to a table, example the table supplied by SUMMARIZE.
SUMMARIZECOLUMNS - Use it to run queries in DAX Studio or create Calculated Table but not measures and calculated columns
GROUPBY - Similar to SUMMARIZE in grouping but different in internal implementation and can only use CURRENTGROUP to get SUM, MIN, MAX etc using an iterator function. Use of CALCULATE is not allowed.
RANKX has to build an internal lookup table so if you have 10 brand in a Matrix and you use ALL then all those 10 brands are used to build a table that will contain let’s say Sales Amount for each Brand this lookup table is built using Context Transition where CALCULATE transforms the currently iterated row into an equivalent filter context, RANKX has to evaluates the same measure used in the filter context as well.
RANKX perform 2 important steps
Evaluate the same measure [sum of values] in the row context of the table supplied All(Products[Brand])
Evaluate the same measure in external filter context so that it can compare this value against the internal table prepared in step1.
But if you don’t use ALL or ALLSELECTED then RANKX will prepare a lookup table of 1 Brand and Will compare the same value from step 2 against same value from step 1, that’s why you will see the RANK as 1
And at Grand Total you will get 1 because 3.8 Billion is greater than any value in the table prepared in Step 1
