Lookup between virtual tables without relationship

talsed — Wed, 05 Jul 2023 04:05:32 GMT

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!

Re: Lookup between virtual tables without relationship

johnt75 — Wed, 05 Jul 2023 09:45:05 GMT

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

Re: Lookup between virtual tables without relationship

talsed — Thu, 06 Jul 2023 07:37:04 GMT

thanks but it didn't help.

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

topic Re: Lookup between virtual tables without relationship in DAX Commands and Tips

Lookup between virtual tables without relationship

Re: Lookup between virtual tables without relationship

Re: Lookup between virtual tables without relationship