09-23-2020 21:51 PM
Suppose you had a pre-aggregated table and you needed to reverse engineer the original, non-aggregated data in order to calculate the median value of the original data? This was the situation presented by @s45kougo in this thread. Sound impossible? Nope, just a bit of tricky GENERATE and GENERATESERIES interaction. The blown out table in the solution (Table 2) is presented solely for illustrative purposes. The actual blowout of the table is actually handled completely within the DAX measure.
Blowout! = VAR __Table = GENERATE( 'Table', VAR __Quantity = [Quantity] RETURN GENERATESERIES(1,__Quantity,1) ) RETURN MEDIANX(__Table,[Price])
Because the second table in a GENERATE function is evaluated within the context of each row of the first table, we can grab the quantity and simply use GENERATESERIES to return a table with the same number of rows as the quantity as our second table. This in effect recreates the original, pre-aggregated data. We can then use an iterator function to iterate over this table and calculate the original median value in this case.