DaxStudio - Implementing data lineage with table variables

Question

I have 2 problems :

When running the code below with DaxStudio, I get an error along the lines of "TREATAS function expects a complete column reference" ; How can I implement data lineage with table variables not coming from a table of a data model?
Is there a simple way of adding to tbl_ITEMS_REFS the consolidated sum of tbl_SALES[AMOUNT]?

The 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"}
        }
    )
    

VAR tbl_ITEMS_DATA_LINEAGE  =
        TREATAS (
            SELECTCOLUMNS ( tbl_ITEMS_REFS , [Code] )
        ,   SELECTCOLUMNS ( tbl_SALES , [ITEM_CODE] )
        )


RETURN
    ADDCOLUMNS (
        tbl_ITEMS_DATA_LINEAGE
    ,   "Total_Sales"
    ,       CALCULATE (
                SUM ( [AMOUNT] )
            )
    )

Thank you for your help.

Addendum

For question 2, by "adding to tbl_ITEMS_REFS", I was thinking of a simple way to get something like this :

Department 1    X   90
Department 1    Y   40
Department 2    Z   60

score 1 · Accepted Answer · answered Aug 29 '22 at 10:25

1

TREATAS won't work with virtual tables. If you add tbl_SALES to an actual data model so it is a real physical table and connect DAX Studio to it, the code works fine as long as you change the following:

EVALUATE




VAR tbl_ITEMS_REFS =
    DATATABLE(
        "Origin"    ,   STRING
    ,   "Code"      ,   STRING
    ,   {
            {"  Department 1    "       ,   "X"}
        ,   {"  Department 1    "       ,   "Y"}
        ,   {"  Department 2    "       ,   "Z"}
        }
    )
    

VAR tbl_ITEMS_DATA_LINEAGE  =
        TREATAS (
            SELECTCOLUMNS ( tbl_ITEMS_REFS , [Code] )
        ,    tbl_SALES[ITEM_CODE] 
        )


RETURN
    ADDCOLUMNS (
        tbl_ITEMS_DATA_LINEAGE
    ,   "Total_Sales"
    ,       CALCULATE (
                SUM ( tbl_SALES[AMOUNT] )
            )
    )

answered Aug 29 '22 at 10:25

Davide Bacci

16,647
3
10
36

Thank you for your reply. For the first question, I was wondering if I could skip the 'add o data model' part but you answered tha just fine. However, for my second question, I was eager to know if there existed a simple way of "importing" the Origin column into my result table? – Frederi ROSE Aug 31 '22 at 14:37
I don't follow. Can you not just import the data in power query and either do a join or create a relationship? – Davide Bacci Aug 31 '22 at 16:04
Yes I could indeed do it like that.But, for my personal knowledge, I wished to know if this was possible only with DAX code and temporary tables. – Frederi ROSE Aug 31 '22 at 16:09
You can probably do it in DAX using naturalleftouterjoin() or something similar but it's not something you'd do in the real world. If you definitely want a DAX solution, post a new question with input data and expected output and someone will provide an answer if I don't get there first. – Davide Bacci Aug 31 '22 at 16:16

DaxStudio - Implementing data lineage with table variables

Addendum

1 Answers1