Making row value equal to Column Name

[Anonymous]

Hi, I can't figure out how to solve this dax problem of mine. I have searched the forum but could not find an answer. 

Given tables like this which are related one to Many (from left to right):
Table 1                                                            Table 2

                  

I know the right table only has the Name Value appearing only once per name but I created this simplistic example to help portray my problem.

What I want to do is to be able to add a Calculated Column that basically says that: if a value in column 'Code' is equal to the name of column '0191' or column '5191' or column '4221', then multiply Sales by the specific figure (from table 1) depending on Name and column name.

Is this possible?


Best Regards.

[Anonymous]

I would suggest restructuring your reference table like this:

Name	Code	Value
John	0191	2
Mark	0191	5

And so forth. You can do this easily in the Power Query editor. (lookup how to pivot/unpivot tables)

Then you could write a formula like this:

Measure = 
VAR code = SELECTEDVALUE(Sales[code])
VAR name = SELECTEDVALUE(Sales[name])
RETURN
CALCULATE(
SUM(Table1[Value]),
Table1[code] = code && Table1[name] = name)
* Sales[Sales]

[Anonymous]

Thanks for the reply cstaulbee.

Unfortunately, the values in Table 1 from columns '0191', '5191', and '4221' are calculated columns. If I'm not mistake, I can only pivot tables from the original data source correct? If this is true, how should I go about solving this problem?

[Anonymous]

You could create calculated columns like the 2 that I gave as an example. I'm not sure what the data looks like that you're building the calculated columns off of so it's hard to say how you could do that.