topic Re: How to use the non base table (custom table) columns? in DAX Commands and Tips

How to use the non base table (custom table) columns?

tchiang7 — Fri, 15 Nov 2024 16:22:52 GMT

I found this example in the forum. It was posted in 2018.

Measure 2 = 
VAR TableVar =
    ADDCOLUMNS(
        SUMMARIZE (
            'Product'
            ,'Product'[ProductKey]
            ,'Product'[Color]
        )
        ,"aColumn", 1
    )
RETURN
    SUMX(
        TableVar
        ,[aColumn]
    )

It was working in 2018 but not working now. The "[aColumn]" cannot be resolved by DAX becaue TableVar is not a base table (any table defined in Data Model). Is any workaround available except creating the custom table in data model?

Re: How to use the non base table (custom table) columns?

johnt75 — Fri, 15 Nov 2024 16:37:50 GMT

That should still work. Can you post the code of the actual measure you're having trouble with ?

Re: How to use the non base table (custom table) columns?

tchiang7 — Fri, 15 Nov 2024 17:15:19 GMT

My usecase is using NATURALLEFTOUTERJOIN() to join two tables together, and apply SUMMARIZE with SUM() function. My problem is DAX won't recognize any column name included in the result table. Therefore the syntax of "my table"[column name] will not work.

Re: How to use the non base table (custom table) columns?

tchiang7 — Fri, 15 Nov 2024 20:10:29 GMT

Thank you.

Here is the example:

EVALUATE
VAR TableVar =
DataTable("Name", STRING,
"Region", STRING,
"aColumn", DOUBLE
, {
{" User1","East", 1.0},
{" User2","East", 1.5},
{" User3","West", 1.6},
{" User4","West", 0.7},
{" User4","East", 3.1}
}
)
RETURN
SUMMARIZE(TableVar,"Total",
SUM('TableVar'[aColumn])
)

This is the error message:

Table variable 'TableVar' cannot be used in current context because a base table is expected.

Re: How to use the non base table (custom table) columns?

VahidDM — Sun, 17 Nov 2024 07:06:33 GMT

Hi tchiang7

To resolve this issue without adding custom tables to your data model, you can adjust your DAX code to avoid passing table variables where base tables are expected. Here's how:

1. Use Table Expressions Directly

Instead of storing the table in a variable, use the table expression directly within your function.

Measure 2 = SUMX( ADDCOLUMNS( SUMMARIZE( 'Product', 'Product'[ProductKey], 'Product'[Color] ), "aColumn", 1 ), [aColumn] )

Explanation:
- Removed the VAR table variable.
- Passed the table expression directly into SUMX.
- [aColumn] is now recognized within the context of SUMX.

2. Use Functions That Accept Table Expressions

Some functions, like SUMMARIZECOLUMNS, accept table expressions and can work with table variables indirectly.

Example Using SUMMARIZECOLUMNS:

EVALUATE SUMMARIZECOLUMNS( 'Product'[Color], "Total", SUM('Product'[aColumn]) )

Note: Ensure that 'Product'[aColumn] exists in the base table or is defined within the context.

3. Use `CURRENTGROUP()` in `SUMMARIZE`

When using SUMMARIZE, you can leverage CURRENTGROUP() to reference the current group in the row context.

Modified EVALUATE Statement:

EVALUATE SUMMARIZE( DATATABLE( "Name", STRING, "Region", STRING, "aColumn", DOUBLE, { {"User1", "East", 1.0}, {"User2", "East", 1.5}, {"User3", "West", 1.6}, {"User4", "West", 0.7}, {"User4", "East", 3.1} } ), [Region], "Total", SUMX(CURRENTGROUP(), [aColumn]) )

Explanation:
- CURRENTGROUP() refers to the subset of rows in the current group defined by SUMMARIZE.
- This allows you to perform calculations on grouped data without referencing a table variable.

4. Use `DEFINE` in DAX Queries

When using EVALUATE in tools like DAX Studio, you can define variables using DEFINE:

DEFINE VAR TableVar = DATATABLE( "Name", STRING, "Region", STRING, "aColumn", DOUBLE, { {"User1", "East", 1.0}, {"User2", "East", 1.5}, {"User3", "West", 1.6}, {"User4", "West", 0.7}, {"User4", "East", 3.1} } ) EVALUATE SUMMARIZE( TableVar, [Region], "Total", SUMX(CURRENTGROUP(), [aColumn]) )

Explanation:
- DEFINE allows you to declare variables accessible in the EVALUATE statement.
- Ensures that the variable TableVar is recognized in the query.

Key Takeaways:

Avoid Passing Table Variables to Functions Expecting Base Tables:
- Functions like SUMMARIZE and SUM require base tables.
- Passing a table variable directly causes errors.
Use Table Expressions Inline:
- Incorporate your table-generating expressions directly within your calculations.
Leverage CURRENTGROUP() for Grouped Calculations:
- Allows you to perform aggregations within SUMMARIZE without referencing external variables.
Use DEFINE for Variables in DAX Queries:
- In query contexts, DEFINE makes variables available for use in EVALUATE.

By adjusting your DAX code to align with the current requirements—avoiding the use of table variables where base tables are expected—you can achieve the desired calculations without adding custom tables to your data model.

Example Applying the Solution to Your Scenario:

-- Original measure adjusted to work without table variables Measure 2 = SUMX( ADDCOLUMNS( SUMMARIZE( 'Product', 'Product'[ProductKey], 'Product'[Color] ), "aColumn", 1 ), [aColumn] )

This approach should resolve the errors and allow your measure to function correctly.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: How to use the non base table (custom table) columns?

johnt75 — Mon, 18 Nov 2024 09:28:21 GMT

There's a couple of issues here. I'll address them in no particular order.

Never use SUMMARIZE to create calculated columns. Use SUMMARIZE for grouping, but if you need to create calculated columns then use ADDCOLUMNS to do that part.

You can't use SUMMARIZE on table variables, only on tables which are part of the model. If you want to do grouping of non-model tables, you can use GROUPBY with the CURRENTGROUP function. You could rewrite your code like

EVALUATE VAR TableVar = DATATABLE ( "Name", STRING, "Region", STRING, "aColumn", DOUBLE, { { " User1", "East", 1.0 }, { " User2", "East", 1.5 }, { " User3", "West", 1.6 }, { " User4", "West", 0.7 }, { " User4", "East", 3.1 } } ) RETURN GROUPBY ( TableVar, "Total", SUMX ( CURRENTGROUP (), [aColumn] ) )

topic Re: How to use the non base table (custom table) columns? in DAX Commands and Tips

How to use the non base table (custom table) columns?

Re: How to use the non base table (custom table) columns?

Re: How to use the non base table (custom table) columns?

Re: How to use the non base table (custom table) columns?

Re: How to use the non base table (custom table) columns?

1. Use Table Expressions Directly

2. Use Functions That Accept Table Expressions

3. Use CURRENTGROUP() in SUMMARIZE

4. Use DEFINE in DAX Queries

Re: How to use the non base table (custom table) columns?

3. Use `CURRENTGROUP()` in `SUMMARIZE`

4. Use `DEFINE` in DAX Queries