How to implement dynamic lookup tables

[amir_tohidi]

Hi,

I need to create a (dynamic?) lookup table in my data model. The inputs are two values: type and benefit. The output is an AccountType. I have pasted an excerpt from the code below.

Initially I thought I'd create a table with the three columns;

type              benefit           AccountType

But dealing with default values in the switch statements becomes a problem as I would need to repeat so many rows. For example:

type              benefit           AccountType

asset30         master           MASTER

asset30                               SAVINGS-FLEXI

ast30i2          master           MASTER

ast30i2                                SAVINGS-FLEXI

ast30i3          master           MASTER

ast30i3                                SAVINGS-FLEXI

In the SQL Server Reporting Services world, I would write a function to provide dynamic lookup using the two input to overcome this problem.

How can I implement this in Power BI? 

Code excerpt

get_Account_Type(char(08) type, char(08) benefit)
{
	switch type
	{
		case  "asset30"	:
		case  "ast30i2"	:
		case  "ast30i3"	:

			switch benefit
			{
				case  "master" :
					return  "MASTER";		 break;

				default:
					return  "SAVINGS-FLEXI"; break;
			}

		case  "bna95"  :

			switch benefit
			{
				case  "basenp" :
				case  "nonpers":
					return  "SAVINGS-FLEXI";		break;

				default:
					return  makestring("UNKNOWN BENEFIT TYPE: ",benefit);
			}

[amir_tohidi]

Should I use a DAX function that effectively translates the switch statements into the DAX equivalent (whatever that may be)?

In other words, instead of a dynamic lookup table, use a dynamic lookup function?

[amir_tohidi · Accepted solution]

Thank to this article, I ended up doing this as two DAX functions in my Power Query (see below - not all combinations shown):

let

fnERASE_AccountType1 = (input) => let
 values = {

 {"asset30master",     "MASTER"},
 {"ast30i2master",     "MASTER"},
 {"ast30i3master",     "MASTER"},

 {"bna95basenp",       "SAVINGS-FLEXI"},
 {"bna95nonpers",      "SAVINGS-FLEXI"},

 {"clientnelsmast",    "CLOSE WEALTH"},
 {"clientnelsons",     "CLOSE WEALTH"},
 {"clientnelsspec",    "CLOSE WEALTH"},

 {input,               "XXXXX"}

 },
 Result = List.First(List.Select(values, each _{0}=input)){1}
 in
 Result,

fnERASE_AccountType2 = (input) => let
 values = {

 {"asset30",           "SAVINGS-FLEXI"},
 {"ast30i2",           "SAVINGS-FLEXI"},
 {"ast30i3",           "SAVINGS-FLEXI"},

 {"bna95",             "UNKNOWN BENEFIT TYPE"},

 {input,               "YYYYY"}

 },
 Result = List.First(List.Select(values, each _{0}=input)){1}
 in
 Result,

    Source = Sql.Database("SQLSERVer\inst80", "prodcapbdb", [Query="-- dmd#(lf)select#(tab)#(tab)rowno,#(lf)#(tab)#(tab)rowno_custdmd_cust,#(lf)#(tab)#(tab)csan,#(lf)#(tab)#(tab)type,#(lf)#(tab)#(tab)benefit,#(lf)#(tab)#(tab)sdate as 'OpenedDate'#(lf)from#(tab)#(tab)dmd#(lf)"]),
    #"Added Custom" = Table.AddColumn(Source, "ERASE_AccountType", each if(fnERASE_AccountType1([type] & [benefit]) <> "XXXXX") 
then fnERASE_AccountType1([type] & [benefit]) 
else fnERASE_AccountType2([type]))
in
    #"Added Custom"

[amir_tohidi]

The code didn't paste properly so here is again

let

fnERASE_AccountType1 = (input) => let
 values = {

 {"asset30master",     "MASTER"},
 {"ast30i2master",     "MASTER"},
 {"ast30i3master",     "MASTER"},

 {"bna95basenp",       "SAVINGS-FLEXI"},
 {"bna95nonpers",      "SAVINGS-FLEXI"},

 {"clientnelsmast",    "CLOSE WEALTH"},
 {"clientnelsons",     "CLOSE WEALTH"},
 {"clientnelsspec",    "CLOSE WEALTH"},

 {input,               "XXXXX"}

 },
 Result = List.First(List.Select(values, each _{0}=input)){1}
 in
 Result,

fnERASE_AccountType2 = (input) => let
 values = {

 {"asset30",           "SAVINGS-FLEXI"},
 {"ast30i2",           "SAVINGS-FLEXI"},
 {"ast30i3",           "SAVINGS-FLEXI"},

 {"bna95",             "UNKNOWN BENEFIT TYPE"},

 {input,               "YYYYY"}

 },
 Result = List.First(List.Select(values, each _{0}=input)){1}
 in
 Result,

    Source = Sql.Database("SQLSERVer\inst80", "prodcapbdb", [Query="-- dmd#(lf)select#(tab)#(tab)rowno,#(lf)#(tab)#(tab)rowno_custdmd_cust,#(lf)#(tab)#(tab)csan,#(lf)#(tab)#(tab)type,#(lf)#(tab)#(tab)benefit,#(lf)#(tab)#(tab)sdate as 'OpenedDate'#(lf)from#(tab)#(tab)dmd#(lf)"]),
    #"Added Custom" = Table.AddColumn(Source, "ERASE_AccountType", each if(fnERASE_AccountType1([type] & [benefit]) <> "XXXXX")
then fnERASE_AccountType1([type] & [benefit])
else fnERASE_AccountType2([type]))
in
    #"Added Custom"