DaxStudio表变量数据沿袭实现问题及销售额汇总需求

1. Fixing "TREATAS函数需要完整列引用" Error & Implementing Data Lineage with Table Variables #

The error occurs because TREATAS requires the second argument to be a physical column from the data model, not a calculated column generated by SELECTCOLUMNS on a table variable. Since both tbl_SALES and tbl_ITEMS_REFS are temporary table variables (not part of the model), TREATAS can’t establish lineage between them directly. Use one of these two methods to link the variables and calculate the desired sum:

Method 1: Direct Filter in CALCULATE #

Iterate over tbl_ITEMS_REFS with ADDCOLUMNS, and filter tbl_SALES directly in the CALCULATE statement to match the item code:

EVALUATE
VAR tbl_SALES =
    DATATABLE(
        "SALES_NBR", INTEGER,
        "ITEM_CODE", STRING,
        "AMOUNT", INTEGER,
        {
            {1, "X", 10}, {2, "Y", 10}, {3, "Z", 10},
            {4, "X", 20}, {5, "Z", 20}, {6, "Z", 10},
            {7, "Z", 20}, {8, "X", 30}, {9, "X", 30},
            {10, "Y", 30}
        }
    )
VAR tbl_ITEMS_REFS =
    DATATABLE(
        "Origin", STRING,
        "Code", STRING,
        {
            {"  Department 1    ", "X"},
            {"  Department 1    ", "Y"},
            {"  Department 2    ", "Z"}
        }
    )
RETURN
SELECTCOLUMNS(
    ADDCOLUMNS(
        tbl_ITEMS_REFS,
        "Total_Sales",
        CALCULATE(
            SUM(tbl_SALES[AMOUNT]),
            tbl_SALES[ITEM_CODE] = [Code]
        )
    ),
    "Department", TRIM([Origin]),
    "Code", [Code],
    "Total_Sales", [Total_Sales]
)

Method 2: Aggregate & Join Tables #

First aggregate sales by item code, then join with the reference table using NATURALINNERJOIN (after aligning column names):

EVALUATE
VAR tbl_SALES =
    DATATABLE(
        "SALES_NBR", INTEGER,
        "ITEM_CODE", STRING,
        "AMOUNT", INTEGER,
        {
            {1, "X", 10}, {2, "Y", 10}, {3, "Z", 10},
            {4, "X", 20}, {5, "Z", 20}, {6, "Z", 10},
            {7, "Z", 20}, {8, "X", 30}, {9, "X", 30},
            {10, "Y", 30}
        }
    )
VAR tbl_ITEMS_REFS =
    DATATABLE(
        "Origin", STRING,
        "Code", STRING,
        {
            {"  Department 1    ", "X"},
            {"  Department 1    ", "Y"},
            {"  Department 2    ", "Z"}
        }
    )
VAR aggregated_sales =
    SELECTCOLUMNS(
        SUMMARIZE(tbl_SALES, tbl_SALES[ITEM_CODE], "Total_Sales", SUM(tbl_SALES[AMOUNT])),
        "Code", [ITEM_CODE],
        "Total_Sales", [Total_Sales]
    )
RETURN
SELECTCOLUMNS(
    NATURALINNERJOIN(tbl_ITEMS_REFS, aggregated_sales),
    "Department", TRIM([Origin]),
    "Code", [Code],
    "Total_Sales", [Total_Sales]
)

2.简便方法为tbl_ITEMS_REFS添加tbl_SALES[AMOUNT]的合并汇总 #

Both methods above directly produce your desired output. The core steps are:

1. Aggregate tbl_SALES by ITEM_CODE to get total amounts per item.
2. Link the aggregated sales data to tbl_ITEMS_REFS using the matching item code.
3. Trim the Origin column to remove extra spaces and format output columns as needed.

The second method (aggregate first then join) is more efficient for large datasets, as it reduces the number of iterations required.

内容的提问来源于stack exchange，提问作者Frederi ROSE