Forum Discussion
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', '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.
7 Replies
- Fowmy
Super User
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 🙂
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 Fowmy -- this is the data relationship i have at the moment
- Greg_Deckler
Community Champion
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
