Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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/823601

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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,

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        v-lid-msft 

         

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