Forum Discussion

LauraBueno's avatar
LauraBueno
Helper III
5 years ago

Look up value Column in DirectQuery Mode

Hello,

 

I have two tables in Direct Query mode related via a Many to One relationship (Please see below). I would like to find the value within column 'MinMax' column from Table 1 in the column 'Id' in Table 2 and retrieve the value within column 'Higher' of Table 2.

 

If I use the expression below just for a single valuer from MinMax column of table 1, it works:

 

Test = LOOKUPVALUE(Table 2['Higher'], Table 2['Id'],143)

 

However I don't know how I could modify the expression above to do all values within MinMax column of Table1.

 

Any help would be really appreciated!

 

thanks!

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    You cannot use LOOKUPVALUE() as it cannot handle your M:M ambiguity (it can only return a scalar value, not a table).  Use TREATAS() instead.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI LauraBueno,

    AFAIK, current power bi has limited most of Dax functions usage in calculated column/table if you are work with 'direct query' mode. I'd like to suggest you use measure expression instead.
    To extract the current row content, you can try to use aggregate function min/max or selectedvalue functions.

    Regards,

    Xiaoxin Sheng