Dynamic LOOKUPVALUE for multiple value combinations

DAX DAX Calculations
1

Is there a better way to have a calculated column that can perform a lookup (per student) for when an undergraduate and graduate plan code value exists within the same column? There are a total of 14 programs that are a combination of one undergraduate plan and one graduate plan. The method I have started below is working so far…however, there are more variables that need to be created and will involve more nested IF functions. What would be the best way to have a dynamic lookup each for an undergraduate and graduate plan?

Blockquote

4+1 (3) =
VAR StudentID = ‘4+1 WW Students’[Student ID]
VAR HasBSA =
(
LOOKUPVALUE (
‘4+1 WW Students’[Acad Plan (groups)],
‘4+1 WW Students’[Student ID], StudentID,
‘4+1 WW Students’[Acad Plan (groups)], “WBSA”
) = “WBSA”
)
VAR HasMSA =
(
LOOKUPVALUE (
‘4+1 WW Students’[Acad Plan (groups)],
‘4+1 WW Students’[Student ID], StudentID,
‘4+1 WW Students’[Acad Plan (groups)], “WMSA”
) = “WMSA”
)
VAR HasBSTM =
(
LOOKUPVALUE (
‘4+1 WW Students’[Acad Plan (groups)],
‘4+1 WW Students’[Student ID], StudentID,
‘4+1 WW Students’[Acad Plan (groups)], “WBSTM”
) = “WBSTM”
)
VAR HasMSM =
(
LOOKUPVALUE (
‘4+1 WW Students’[Acad Plan (groups)],
‘4+1 WW Students’[Student ID], StudentID,
‘4+1 WW Students’[Acad Plan (groups)], “WMSM”
) = “WMSM”
)
VAR HasMSISA =
(
LOOKUPVALUE (
‘4+1 WW Students’[Acad Plan (groups)],
‘4+1 WW Students’[Student ID], StudentID,
‘4+1 WW Students’[Acad Plan (groups)], “WMSISA”
) = “WMSISA”
)
VAR Result =
IF (
HasBSA
&& HasMSA,
“BS Aeronautics to MS Aeronautics”,
IF(
HasBSTM && HasMSM,
“BS Technical Management to MS Management”,
IF(
HasBSTM && HasMSISA,
“BS Technical Management to MS Information Security & Assurance”,
IF(
(HasBSTM && HasMSM || HasMSISA = BLANK()) || (HasBSA && HasMSA = BLANK()),
“Missing Graduate Plan”,
“Not 4+1 / Pre 4+1”)
)
)
)

RETURN
Result

Blockquote

2

It’s hard to say exactly, but this formula looks overly complex.

If you need to do something this complex there has to be something wrong with your model in my view.

I would have a real think about how you can optimise or simplify your data model so that you don’t need to do this with LOOKUPVALUE. You may be able to rearrange your tables where a simple relationship will create the filtering and look up that you require.

At all times you want to avoid creating complex calculated columns, or even measures realistically, when some simplification in your model can go along way to solving this for you.

Sam