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

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