measure to look up multiple values from multiple colums

[Anonymous]

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', 'asset'

Column '2020 SPEND' is from the Table "Spend"

Predicament = 

I would like to write a measure to populate the KMs for each 'ASSET' row. by looking up the KMs from the KM TABLE using KM.ID as a the look up Value. 

EG - I would like the KM 73,211 polulated for ASSET 891A & 891B. currently its Blank.. hence the total is not correct either. 

thanks in advance. 

[Greg_Deckler]

@Anonymous - You should be able to use RELATED or RELATEDTABLE if your tables are related, otherwise you would want to use LOOKUPVALUE. You might need to use the Lookup Min/Max pattern: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

You will also likely end up having a measures total issue, very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Fowmy]

@Anonymous 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

hello Fomy,

Below is the sample of my 3 data tables. .

Issue is, I get the KM file from the custmer for KM.ID only. 

Vehicle master table tell me the associated Asset name to which i have to copy the KM from the KM.ID.

I need to add a column in my invoice table to add the KM for each ASSET NAME. 

EG .. in JAN. I want KM to be populated for 501 and its trailers (751A and 751B)

therefore Expected result should be as below..

501 = 1000KMS

751A = 1000 KMS

751B = 1000 KMS

Invoice file

Invoice Date	Invoice number	KM.ID	ASSET NAME	Amount
JAN - 2020	J1	501	501	$1000
JAN - 2020	J2	501	751A	$1000
JAN - 2020	J3	501	751B	$1000
FEB - 2020	F1	501	501	$1000
FEB - 2020	F2	501	751A	$1000
FEB - 2020	F3	501	751B	$1000
MAR - 2020	M31	501	501	$1000
MAR - 2020	M32	501	751A	$1000
MAR - 2020	M33	501	751B	$1000
APR - 2020	A1	501	501	$1000
APR - 2020	A2	501	751A	$1000
APR - 2020	A3	501	751B	$1000
MAY - 2020	M51	501	501	$1000
MAY - 2020	M52	501	751A	$1000
MAY - 2020	M53	501	751B	$1000

KM FILE

Date	KM.ID
JAN -2020	501	1000
FEB -2020	501	2000
MAR -2020	501	3000
APR-2020	501	4000

VEHCILE MASTER

Asset NAME	KM.ID
501	501
751A	501
751B	501

[Anonymous]

@Fowmy  -- this is the data relationship i have at the moment

[Greg_Deckler]

@Anonymous - I am not clear at all in terms of what you want you column to look like in you Invoice table. Can you post your Invoice table again with the new column in terms of what you are expecting for each row?

[Anonymous]

Hi @Greg_Deckler ,

Does the below table provides the clarification needed? 

Invoice Date	Invoice number	KM.ID	ASSET NAME	Amount	KM (current Display)	KM (What i want to display)
JAN - 2020	J1	501	501	$1000	1000	1000
JAN - 2020	J2	501	751A	$1000	-	1000
JAN - 2020	J3	501	751B	$1000	-	1000

[Greg_Deckler]

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