Forum Discussion
Referencing table as a variable
Hi guys, I'm stuck with this and getting opposing information online.
I'm wondering can I make calculated table through use of variable?
Code is like this:
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 =
SUMMARIZE (
Table1,
Table1[Brand_Promocija],
"Active weeks", COUNTA ( Table1[TRP] ),
"Total TRP", SUM ( Table1[TRP] ),
"TRP/Week",
DIVIDE (
SUM ( Table1[TRP] ),
COUNTA ( Table1[TRP] )
)
)
RETURN
Result
So in essence I want to define table as a variable so I could summarize it furhter in resulting calculated table.
As fara as I can see, this is not possible. 😞 Only way this works is if I make calculated table in model and summarize it again as another table.
This makes two tables, and I was thinking of optimizing process so I could have only one 🙂
On internet I can find some code that uses two or more table variables, referencing one another in the process and evaluating the end (maybe its because it is a measure?)
So please help getting this right as I feel its somehow basic rule of DAX 🙂
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
6 Replies
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.
please try
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,
Table1[Brand_Promocija],
"Active weeks", COUNTX ( CURRENTGROUP (), [TRP] ),
"Total TRP", SUMX ( CURRENTGROUP (), [TRP] )
),
"TRP/Week", DIVIDE ( [Total TRP], [Active weeks] )
)
RETURN
ResultNope,
Table1[Brand_Promocija] cannot be found in the input table.
Also all [TRP] instances are also cannot be referenced.
That is what is puzzeling.
What could be the problem?
If I make two separate tables, everything works 😞
I guess I have to use iteration functions?
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
Ok, thank you. .I think I get it now. This was a huge help since I'm trying to do this for a while now 🙂
Much obliged!
