Forum Discussion
SWITCH using conditions from two tables
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!!
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.
8 Replies
I'm trying to create a new column to rename products based on columns from two different tablesSELECTEDVALUE has no meaning for columns. It can only be used in measures.
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.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
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.
