Lookup between virtual tables without relationship

[talsed]

Hi guys,

I am quite new to DAX magic and been practicing a lot in the last few months, but still I can't fully understand the concept of lookup values based on columns from "virtual tables", even though I've read the articles about it, and also about the TREATAS function that probably should help here. No matter which method i've used in my code to add the column of lookup value from one table to another, I'm getting the same message of "table variable "xxx" cannot be used in this concept, because a base table is expected" 😕

Anyway, my example is very simple: 

 DEFINE
    VAR _size =
        ADDCOLUMNS (

          SUMMARIZECOLUMNS ( 'person'[person id] ), 

          "total", 'measures'[total]

         )
    VAR _tbl =
        ADDCOLUMNS (
            SUMMARIZECOLUMNS ( 'person'[person id], 'mgr'[mgr id] ),
            "lookup", LOOKUPVALUE ( '_size'[total], '_size'[person id], '_tbl'[mgr id], 0 )
        )

EVALUATE
_tbl

definitions:

'person' and 'mgr' are actual physical tables, [total] is a meassure as part of the model.

so the idea is to add a new column to _tbl1 called "lookup" that will bring the [total] for each [mgr id] matching [mgr id] to [person id].

so as mentioned the error i get is about the fact that those tables are not physical ones in my model, but a virtual temp tables that i'm creating in thie query.

so what's the method that can be used to do a lookup between non-related virtual table variables? 

thanks!

[johnt75]

I think you can do the whole thing in 1 step without needing the first table

DEFINE
    VAR _tbl =
        ADDCOLUMNS (
            SUMMARIZE ( 'person', 'person'[person id], 'mgr'[mgr id] ),
            "mgr size",
                VAR CurrentManager = 'mgr'[mgr id]
                VAR Result =
                    CALCULATE (
                        [total],
                        REMOVEFILTERS ( 'mgr'[mgr id] ),
                        TREATAS ( { CurrentManager }, 'person'[person id] )
                    )
                RETURN
                    Result
        )

EVALUATE
_tbl

[talsed]

thanks but it didn't help. 

also it's getting error as there are 2 different tables of 'person' and 'mgr' in SUMMARIZE function.