Forum Discussion
vgeldbr
Helper IV
Helper IVUsing a variable as an operator in a Power BI query filter
This may not be very elegant but would appreciate some help to find a solution. I have set up a Parameter (SubPortfolio Filter) to filter a query based on the values in a column. I have another P...
Hi vgeldbr
This should work
Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparerList(SubPortfolioFilterList, [SubPortfolio], if SubPortfolioFilterType then "=" else "<>"))
Kind regards
JB
vgeldbrHelper IV
Helper IVThis is fantastic. One last piece of logic I'm missing. I have a parameter (SubPortfolio Filter Type) which can be TRUE or FALSE. This allows the user to specify if subportfolio filter includes or excludes the list of SupPortfolios. My code is below now but I can't figure where to set this as your example is assuming always <>.
let
//Variables - Portfolio:
PortfolioFilterList = Text.Split(#"Portfolio Filter", ";"),
//Variables - SubPortfolio:
SubPortfolioFilterList = Text.Split(#"SubPortfolio Filter", ";"),
//Variables - filter type:
SubPortfolioFilterType = if #"SubPortfolio Filter Type" = false then "<>" else "=",
fComparer = (param1 as any, param2 as any, c_type as text) =>
if c_type = "=" then param1 = param2 else
if c_type = "<" then param1 < param2 else
if c_type = ">" then param1 > param2 else
if c_type = "<=" then param1 <= param2 else
if c_type = ">=" then param1 >= param2 else
if c_type = "<>" then param1 <> param2 else
null,
fComparerList = (param1 as list, param2 as any, c_type as text) =>
if c_type = "=" then List.Contains(param1,param2) else
if c_type = "<>" then not List.Contains(param1,param2) else null,
//Variables - Portfolio:
PortfolioFilterList = Text.Split(#"Portfolio Filter", ";"),
//Variables - SubPortfolio:
SubPortfolioFilterList = Text.Split(#"SubPortfolio Filter", ";"),
//Variables - filter type:
SubPortfolioFilterType = if #"SubPortfolio Filter Type" = false then "<>" else "=",
fComparer = (param1 as any, param2 as any, c_type as text) =>
if c_type = "=" then param1 = param2 else
if c_type = "<" then param1 < param2 else
if c_type = ">" then param1 > param2 else
if c_type = "<=" then param1 <= param2 else
if c_type = ">=" then param1 >= param2 else
if c_type = "<>" then param1 <> param2 else
null,
fComparerList = (param1 as list, param2 as any, c_type as text) =>
if c_type = "=" then List.Contains(param1,param2) else
if c_type = "<>" then not List.Contains(param1,param2) else null,
Source = Sql.Databases("DERUSVMPITRSQ01.EY.NET\INST1"),
IT_ITRDB_PRD = Source{[Name="IT_ITRDB_PRD"]}[Data],
dbo_VW_PPM_ExportFinancial_FY20 = IT_ITRDB_PRD{[Schema="dbo",Item="VW_PPM_ExportFinancial_FY20"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparer(SubPortfolioFilterList{0}, [SubPortfolio], "<>")),
#"Filtered Rows Against List" = Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparerList(SubPortfolioFilterList, [SubPortfolio], "<>"))
IT_ITRDB_PRD = Source{[Name="IT_ITRDB_PRD"]}[Data],
dbo_VW_PPM_ExportFinancial_FY20 = IT_ITRDB_PRD{[Schema="dbo",Item="VW_PPM_ExportFinancial_FY20"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparer(SubPortfolioFilterList{0}, [SubPortfolio], "<>")),
#"Filtered Rows Against List" = Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparerList(SubPortfolioFilterList, [SubPortfolio], "<>"))
in #"Filtered Rows Against List"
Anonymous
Hi vgeldbr
This should work
Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparerList(SubPortfolioFilterList, [SubPortfolio], if SubPortfolioFilterType then "=" else "<>"))
Kind regards
JB
This should work
Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparerList(SubPortfolioFilterList, [SubPortfolio], if SubPortfolioFilterType then "=" else "<>"))
Kind regards
JB
- vgeldbr
Anonymous thanks for all of your help. You've provided me with all the pieces I needed to achieve what I wanted!