SWITCH using conditions from two tables

[PBInewbie00]

Hello,

Searched all over the forum, but I haven't been able to find a good workaround for my problem. I'm trying to create a new column to rename products based on columns from two different tables - 'Table 1'[CODE] & 'Table 2'[PRODUCT]. For some reason, the [PRODUCT] expression never flows through no matter what function I use. I've tried the normal SWITCH function:

Measure = SWITCH(TRUE(),

CONTAINSSTRING(SELECTEDVALUE('Table 2'[PRODUCT]),"Cash"), "CASH",

CONTAINSSTRING(SELECTEDVALUE('Table 1'[CODE]),"ASST"), "ASST",

CONTAINSSTRING(SELECTEDVALUE('Table 1'[CODE]),"COMM"), "COMM",

CONTAINSSTRING(SELECTEDVALUE('Table 1'[CODE]),"DERIV"), "DERIV",

CONTAINSSTRING(SELECTEDVALUE('Table 1'[CODE]),"LOAN"), "LOAN",

SELECTEDVALUE('Table 1'[CODE]))

I've also tried to do the LOOKUPVALUE function (from a diiferent post) using this [KEY] field that I see is in both tables to try and align the two:

Measure = 

VAR _PRODUCT = LOOKUPVALUE('Table 2'[PRODUCT], 'Table 2'[KEY], SELECTEDVALUE('Table 1'[KEY]))

RETURN

SWITCH(TRUE(),

_PRODUCT = "Cash", "Cash",

SELECTEDVALUE('Table 1'[CODE]))

CODE	PRODUCT	EXPECTED	OUTPUT
ASST	Cash	CASH	ASST
ASST	Assets	ASST	ASST
COMM	Commission	COMM	COMM
DERIV	Derivatives	DERIV	DERIV
DERIV	Cash	CASH	DERIV
LOAN	Cash	CASH	LOAN
LOAN	Loans	LOAN	LOAN

Any help would really be appreciated!!

[Anonymous · Accepted solution]

Hi @PBInewbie00 ,

I ceate a table as you mentioned.

Then I think you can create a new calculated column.

Column = 
SWITCH(
    TRUE(),
    'Table'[PRODUCT] = "Cash" && 'Table'[CODE] = "ASST", "CASH",
    'Table'[PRODUCT] = "Assets" && 'Table'[CODE] = "ASST", "ASST",
    'Table'[PRODUCT] = "Commission" && 'Table'[CODE] = "COMM", "COMM",
    'Table'[PRODUCT] = "Derivatives" && 'Table'[CODE] = "DERIV", "DERIV",
    'Table'[PRODUCT] = "Cash" && 'Table'[CODE] = "DERIV", "CASH",
    'Table'[PRODUCT] = "Cash" && 'Table'[CODE] = "LOAN", "CASH",
    'Table'[PRODUCT] = "Loans" && 'Table'[CODE] = "LOAN", "LOAN",
    BLANK()
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

I'm trying to create a new column to rename products based on columns from two different tables

SELECTEDVALUE has no meaning for columns. It can only be used in measures.

[PBInewbie00]

@lbendlin Hi - thank you for responding. To clarify - I used versions of the formulas for both measures and columns. The ones I wrote out were for when I tried to create measures. Please let me know if you need further info.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[PBInewbie00]

Hi @lbendlin - I provided the table at the end of the my original post with the sample data, expected results, and current results.

Reposting below for convenience. Please let me know if this is not sufficient.

CODE	PRODUCT	EXPECTED	CURRENT
ASST	Cash	CASH	ASST
ASST	Assets	ASST	ASST
COMM	Commission	COMM	COMM
DERIV	Derivatives	DERIV	DERIV
DERIV	Cash	CASH	DERIV
LOAN	Cash	CASH	LOAN
LOAN	Loans	LOAN	LOAN

[lbendlin]

So you have two single column tables?  Where do you want the result to be stored? In one of the tables?

[PBInewbie00]

@lbendlin The two tables have other columns, but I'm trying to pull from those specific columns. I'm ok with storing the results in one of the tables (I wasn't sure if storing it in a specific table was affecting the actual result). Thanks again for your help on this!

[lbendlin]

How are you planning to join the tables? Do they have a common column?