Forum Discussion

talsed's avatar
talsed
Frequent Visitor
3 years ago

Lookup between virtual tables without relationship

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 th...
lookup
Virtual Table
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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's avatar
talsed
Frequent Visitor
3 years ago

thanks but it didn't help. 

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