Forum Discussion
Making row value equal to Column Name
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.
3 Replies
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]