Forum Discussion

abbynie08's avatar
abbynie08
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

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

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!

 

CustomerRoleRequiredRole_2Required_2
1Inside Sales1Sales4
1Inside Tech2Tech5
1Inside Manager3Manager6
1Sales4Sales4
1Tech5Tech 5
1Manager6Manager6
1VP7VP7

 

 

General Comment
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

5 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.

     

    • olimilo's avatar
      olimilo
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      Is this not possible to do as a calculated column? Especially when there's a need to filter the data

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    Does this calculated column formula work?

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

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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