DAX Help on creating a calculated column or mesure that will bring a value from another table

[Anonymous]

Hi;

just to put it out there right away. I am a basic PBI user, reason why I am asking for help on this solution which for most people is problably really easy.

Here is what I need:

I have to tables, "Main_Table" and "Secondary_Table". The tables have related fields ([Asset Type] and [Company]). What I need is bring a value to from the second table into the first table [(Useful Life (Y)] making sure to compare the tables by "asset type" and "company" so that the values from "useful life (y) are specific for each company just like the table defines it. See images below:

This is the second table (excel version) that defines the useful life for of the diff asset types; which they are not the same for all companies as you can see.

and this is that same table Unpivoted in PowerQuery (I just changed the company names for security reasons):

I was thinking that maybe LOOKUPVALUE might work but still do not know how to create such code.

Thanks in advance for all the help.

Mcastro

[Balaraju]

Hi,

Above chart showing program wise target temperature I need to show most used target temperature by particular program.

For example: 01BE - 150 is most used  target temperature like we are seeing in count of target temperature.

I want solution on that.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please try something like below.

It is for creating a new column.

Useful Life (Y) CC =
CALCULATETABLE (
VALUES ( Security_Bridge_CompanyName[Useful Life(Y)] ),
FILTER (
Security_Bridge_CompanyName,
Security_Bridge_CompanyName[Company name] = Account_Final_Report[Company]
&& Security_Bridge_CompanyName[Asset Type] = Account_Final_Report[Asset Type]
)
)

[Anonymous]

Hi Kim;

You did understand my issue perfectly even though I was not that good at explaning it.

Your solution worked perfectly! This is awesome!!!

Thanks so much!

Mcastro.

[bcdobbs]

In power query you can click Merge tables and join them both on the two columns. You should then end up with a single table with all your data.

[Anonymous]

Hi bcdobbs;

Yes! I had already thought about that but it was actually not an option since the "asset type" column is not available in the Account Fina Report Table. The "Asset Type" is also a CC within PBI hence not available in PQ.

But thanks for the reply!

Mcastro