Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

measure to look up multiple values from multiple colums

Hello All, Data model: Column 'Asset', 'vehicle type', 'KM.ID' are from the table "Asset" => "asset' is the unique value Column 'KMs' is from table "KM".. this table contains 'Date', 'KM.ID', ...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Greg_Deckler ,

 

Does the below table provides the clarification needed? 

 

Invoice DateInvoice numberKM.IDASSET NAMEAmountKM (current Display)KM (What i want to display)
JAN - 2020

J1

501

501$100010001000
JAN - 2020

J2

501751A$1000-1000
JAN - 2020J3501751B$1000-1000
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - Maybe, it's tough to say. So then in February all of the rows for February would display $2,000? If that is the case.

 

Column in Invoice table = 
  VAR __Asset = [ASSET NAME]
  VAR __InvoiceDate = [INVOICE DATE]
  VAR __KMID = LOOKUPVALUE('VEHICLE MASTER'[KM.ID],'VEHICLE MASTER'[ASSET NAME],__Asset)
RETURN
  LOOKUPVALUE('KM FILE'[Column],'KM FILE'[KM.ID] = __KMID,'KM FILE'[Date], __InvoiceDate)

 

That's my best guess.