cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pbi07
Helper IV
Helper IV

Lookup values from another table

Hello folks 

 

I have 2 tables - Invoice & invoice received 

Invoice Table will have invoice and line items and invoice received will have invoices but without line item 

Invoice Line Item Vendor
15901  10     17850
15905  10     17800
15910  10     17810
15901  20     17850
15910  20     17810
15901  30     17850

15915  10     17642

 

ID            Reference Index
5F22A77 15901        1
5F22A77 15910        2
5F22A77 15901        3
5F22A77 15905        4

Need to identify the invoices present in invoice received and fetch the index value column. I tried using a column in the visualization with the function and was not succesful. 

Invoice_exist = LOOKUPVALUE(Invoice_recvd[Index],Invoice_recvd[Reference],Invoice[Invoice Number])
 
This resulted in an error : " A table of multiple values was supplied where a single value was expected."

 

Am i using this correct or is there a different option to lookup values . Basically i will have duplicate invoices in both tables which cannot be deleted. 

 

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @Pbi07 ,

 

Sorry for our mistake, we can create a measure to get the releated index value and used it in other visual:

 

Invoice_exist_DQ_Measure = 
VAR I_Num = SELECTEDVALUE('Invoice'[Invoice Number])
RETURN
CALCULATE(
    MAX (Invoice_recvd[Index] ),
    Invoice_recvd[Reference] = I_Num
)

 

Or we can merge with the Recvd table and expand the index column

 

3.jpg4.jpg5.jpg6.jpg

 

Those should work on directquery mode.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-lid-msft
Community Support
Community Support

Hi @Pbi07 ,

 

@amitchandak 's query is great by using the Alternate_Result in LookUpValue function but it miss some parameter, please try to create a calculate column using following dax?

 

Invoice_exist = 
LOOKUPVALUE (
    Invoice_recvd[Index],
    Invoice_recvd[Reference], [Invoice Number],
    FIRSTNONBLANK ( 'Invoice_recvd'[Index], true )
)

 

5.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User IV
Super User IV

Try

Invoice_exist = LOOKUPVALUE(Invoice_recvd[Index],Invoice_recvd[Reference],firstnonblank(Invoice[Invoice Number]))

 

You can also join the invoice and reference and get the data.

 

You can create a table with the distinct invoice and join with both of them

Invoices = summarize(Table1, invoice)

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

@v-lid-msft 

 

Thanks for the solution. But my model is based on a direct query and prevented me with an error - " Function 'LOOKUPVALUE' is not allowed as part of calculated column DAX expressions on DirectQuery models." 

 

 

v-lid-msft
Community Support
Community Support

Hi @Pbi07 ,

 

We can try to create calculated column using max in direct query mode to meet your requirement:

 

Invoice_exist = 
VAR I_Num = [Invoice Number]
RETURN
CALCULATE(
    MAX (Invoice_recvd[Index] ),
    Invoice_recvd[Reference] = I_Num
)


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lid-msft 

 

No. That did not work. Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.

 

 

v-lid-msft
Community Support
Community Support

Hi @Pbi07 ,

 

Sorry for our mistake, we can create a measure to get the releated index value and used it in other visual:

 

Invoice_exist_DQ_Measure = 
VAR I_Num = SELECTEDVALUE('Invoice'[Invoice Number])
RETURN
CALCULATE(
    MAX (Invoice_recvd[Index] ),
    Invoice_recvd[Reference] = I_Num
)

 

Or we can merge with the Recvd table and expand the index column

 

3.jpg4.jpg5.jpg6.jpg

 

Those should work on directquery mode.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors