Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

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. 

 

jackarjunan_0-1596880947718.png

7 REPLIES 7
Greg_Deckler
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Fowmy
Super User
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 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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 DateInvoice numberKM.IDASSET NAMEAmount
JAN - 2020

J1

501

501$1000
JAN - 2020

J2

501751A$1000
JAN - 2020J3501751B$1000
FEB - 2020F1501501$1000
FEB - 2020F2

501

751A$1000
FEB - 2020F3501751B$1000
MAR - 2020M31501501$1000
MAR - 2020M32501751A$1000
MAR - 2020M33

501

751B$1000
APR - 2020A1501501$1000
APR - 2020A2501751A$1000
APR - 2020A3501751B$1000
MAY - 2020M51

501

501$1000
MAY - 2020M52501751A$1000
MAY - 2020M53501751B$1000

 

KM FILE

DateKM.ID 
JAN -20205011000
FEB -20205012000
MAR -20205013000
APR-20205014000

 

VEHCILE MASTER

Asset NAMEKM.ID
501

501

751A501
751B501

 

 

 

Anonymous
Not applicable

jackarjunan_0-1596973121125.png

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.