Forum Discussion
How to implement dynamic lookup tables
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);
}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"
3 Replies
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?
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"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"