Forum Discussion
Referencing table as a variable
Sorry. Typo mistake. Don't use the table reference only use the column reference as temporary tables cannot be referenced. It should work.
EVALUATE
VAR Table1 =
FILTER (
SUMMARIZE (
FILTER ( '_SOURCE CARS', '_SOURCE CARS'[Year] = 2022 ),
Brand_Prodaja[Brand_Promocija],
Datumi[ISO week],
"TRP", [Sum of All 18-54 I&G GRP]
),
[TRP] > 25
&& [Brand_Promocija] <> "Ništa"
)
VAR Result =
ADDCOLUMNS (
GROUPBY (
Table1,
[Brand_Promocija],
"Active weeks", COUNTX ( CURRENTGROUP (), [TRP] ),
"Total TRP", SUMX ( CURRENTGROUP (), [TRP] )
),
"TRP/Week", DIVIDE ( [Total TRP], [Active weeks] )
)
RETURN
Result
Thank you very much, this works now.
Can you just explain why this works, and my version didn't?
Is it because of table references or something other also?
Yes if you delete the table reference you won't receive an error.
However, you also won't obtain correct results. The reason is despite SUMMARIZE can actually group by the temporary virtual table but it cannot perform context transition (no function can in this case). In other words, once the table is grouped by we don't have access to the subset of rows that are grouped by the columns of SUMMARIZE. In order to retrieve these rows we need to filter the complete table for every row of the summary table which will result in an efficient and complex query.
On the other hand hand the GROUOPBY function can retrieve these rows without the need for context transition (thanks to CURRENTGROUP ( ) argument). While grouping by the GROUPBY function keeps records of the subset of rows that belong to each row of the summary (grouped) table. The subset of rows are stored in the CURRENTGROUP ( ) table. By iterating this table we can perform simple aggregations like count, max, average & sum.