topic Referencing table as a variable in DAX Commands and Tips

Referencing table as a variable

AgencyPowerBi — Tue, 14 Mar 2023 13:16:17 GMT

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 🙂

Re: Referencing table as a variable

tamerj1 — Tue, 14 Mar 2023 13:42:50 GMT

Hi AgencyPowerBi

please try

Re: Referencing table as a variable

AgencyPowerBi — Tue, 14 Mar 2023 14:35:09 GMT

Nope,

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?

Re: Referencing table as a variable

tamerj1 — Tue, 14 Mar 2023 16:02:03 GMT

AgencyPowerBi

Sorry. Typo mistake. Don't use the table reference only use the column reference as temporary tables cannot be referenced. It should work.

Re: Referencing table as a variable

AgencyPowerBi — Wed, 15 Mar 2023 08:56:01 GMT

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?

Re: Referencing table as a variable

tamerj1 — Wed, 15 Mar 2023 09:18:38 GMT

AgencyPowerBi

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.

Re: Referencing table as a variable

AgencyPowerBi — Wed, 15 Mar 2023 09:41:08 GMT

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!