topic Re: DAX Help on creating a calculated column or mesure that will bring a value from another table in DAX Commands and Tips

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

Anonymous — Tue, 07 Dec 2021 20:52:53 GMT

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

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

bcdobbs — Tue, 07 Dec 2021 23:27:24 GMT

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.

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

Jihwan_Kim — Wed, 08 Dec 2021 04:11:47 GMT

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]
)
)

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

Anonymous — Wed, 08 Dec 2021 13:53:14 GMT

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.

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

Anonymous — Wed, 08 Dec 2021 13:58:58 GMT

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

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

Balaraju — Wed, 08 Dec 2021 14:24:13 GMT

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.