How to use lookupvalue function for PBI direct query/dual mode

[abbynie08]

Hi all, 

The lookupvalue function does not show in PBI under direct query mode/dual mode. I have been seraching on the forum and did not get answers. I would really appreciate if you can help me with that. So I want to get the Required_2 column by looking up Role_2 column in Role Column and return the required number in the required_2 table. Normally, I can use the lookupvalue function. But under direct query/dual mode, the function does not work.  Some people have suggested changing the settings under file=>options=>direct query, but there is no options to allow the function to work.

Thank you!

Customer	Role	Required	Role_2	Required_2
1	Inside Sales	1	Sales	4
1	Inside Tech	2	Tech	5
1	Inside Manager	3	Manager	6
1	Sales	4	Sales	4
1	Tech	5	Tech	5
1	Manager	6	Manager	6
1	VP	7	VP	7

[v-alq-msft]

Hi, @abbynie08 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @abbynie08 

In DirectQuery, calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. You may create a measure as below,which is the same effect as lookupvalue function.

Measure = 
//LOOKUPVALUE('Table'[Required_2],'Table'[Role_2],SELECTEDVALUE('Table'[Role]))
CALCULATE(
    VALUES('Table'[Required_2]),
    FILTER(
        'Table',
        'Table'[Role_2] = SELECTEDVALUE('Table'[Role])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Does this calculated column formula work?

=CALCULATE(SUM(Data[Required]),FILTER(Data,Data[Role]=EARLIER(Data[Role_2])))

[parry2k]

@abbynie08 seems like you are referring two different tables , can you put sample data of each table and it is bit confusing what you have shown below.