Forum Discussion
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.
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.
Hi Anonymous ,
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
Those should work on directquery mode.
Best regards,
6 Replies
Hi Anonymous ,
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 ) )
Best regards,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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Thanks 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 Anonymous ,
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,
