Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pbi07
Helper V
Helper V

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

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
Super User

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

 

@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." 

 

 

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.

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.