How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask NewITZone Your Own Question
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

If I have two related tables (a common key in each formula),

Customer Question

If I have two related tables (a common key in each formula), what is the DAX formula for creating a measure (not calculated column) in PowerPivot if I want to retrieve one column from a related table, e.g., location column in the Project Value table below?
Table 1 Structure:
Project Location
A L1
B L2
C L3
Table 2 Structure
Project Value
A V1
B V2
C V3
Submitted: 11 months ago.
Category: Microsoft Office
Customer: replied 11 months ago.
My question: What is the Measure formula if I want to add a measure to the Project Value Table, where I want to import the Location column as a measure?
Customer: replied 11 months ago.
Here is the file (in Excel 2016) which has the data model and source data. Please add measure that does the above. Thanks.
Customer: replied 11 months ago.
Note, in first sentence, it should read "a common key in each table" (not formula). Thanks!
Expert:  bbao replied 11 months ago.

Hi There. I am Bing and I look forward to helping you with your issue today. Please give me a bit more time to review your question.

Customer: replied 11 months ago.
OK Bing. This question was intended for someone who is familiar with PowerPivot, SQL and database-related items. I assume that you have a background in this and that the question was properly routed. Thanks!
Expert:  bbao replied 11 months ago.

Sorry I can't update the given Excel file due to a lower version (I am using Excel for Mac 2011). I am opting out and let other experts help you.

Expert:  bbao replied 11 months ago.

Okay as no other experts come in, I am back for a few words.

Basically, your requirement is to make a Pivot Table from data in multiple source or sheets. This can be done. Please check here for the detailed steps.

You may try it yourself. Just hope I have addressed your question. :)

Customer: replied 11 months ago.
No, this is not my question. I am asking for what the measure formula/calculated field formula that imports the column in the related table.
Expert:  bbao replied 11 months ago.

If I understand that correctly, you may get the formula by clicking on the Info button of related field to get the formula used for that field. See the attached screenshot.

Customer: replied 11 months ago.
No. This is not correct. This is not a Pivot Table question! It is a PowerPivot question. Here is some content that helps explain measures.
Expert:  bbao replied 11 months ago.

Okay, as I don't have the right Excel version for your question, I opt out again. Good luck!

Customer: replied 11 months ago.
It appears that you can't answer the question because you are unfamiliar with PowerPivot, NOT because you have the wrong Excel version. Excel 2010 has PowerPivot and this question should be able to be answered by someone familiar with this. Also, since you are acting as an expert here, it may be a good idea to get the latest technology? Please forward to someone who is familiar with this platform and if no one is familiar, please refund any deposit that has been placed on the card. Thank you.
Expert:  Bruce Wilner replied 11 months ago.

I have considerable experience that will enable me to solve this for you. Would you be so kind as to wait until tomorrow morning, when I can attack the problem fresh? It has been a long day, and it is getting late here on the East Coast. Thank you for your anticipated patience and cooperation.

Customer: replied 11 months ago.
Bruce. No worries. Tomorrow works. Thanks.
Expert:  Bruce Wilner replied 11 months ago.

Any particular reason you couldn't use a generic Excel formula? PowerPivot limits you sharply in backward compatibility. I have been able to solve "impossible" problems using routine formulas on many occasions. All it takes is a clever approach.

Customer: replied 11 months ago.
I need the DAX formula for the measure. Thanks
Expert:  Bruce Wilner replied 11 months ago.

You haven't stated what "the measure" is. What is this formula supposed to achieve--precisely?