11-23-2021 06:55 AM
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.
Thanks a lot for your answer. This seems very promising. I'll try to use this, and come back to accept the solution when I understand what I'm doing.
It might take me a bit.
Hi @Greg_Deckler, thanks for this - it's such great advice! I'm looking for something very similar to this, but my final intended result is actually the blown out table in the solution (Table 2). In my case, it is leave application data. For example, if I apply for 3 days of leave starting 2/11, my data only shows start date 2/11, and duration is 3 days. On 3/11, eventhough the person is on leave, it doesn't show. Thus, I would like to add rows that show 3/11 and 4/11 as rows/entries as well.
Would you be able to assist on how to do that? Thank you very much!