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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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


@ 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!:
Mastering Power BI 2nd Edition

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?


@ 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!:
Mastering Power BI 2nd Edition

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.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors