Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
@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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 - 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?
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 |
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
17 | |
14 | |
11 |
User | Count |
---|---|
42 | |
35 | |
25 | |
24 | |
22 |