Merge/Lookup Using Conditions

[sprotson]

I am a complete newbie to Power BI but have some limited SQL knowledge, so please bare with me

My scenario is as follows

Table1 contains a column called type (1,2,3 etc)

Table2 contains 3 columns, Value (1,2,3 etc), DisplayValue (a, b, c, etc) and Code (Code1, Code2, Code3 etc)

I essentially want to lookup the type column from Table1 in Table 2 and return the DisplayValue column if they match and if the code is a certain value.  It would be preferable if type was replaced with DisplayValue, but I could cope with having both columns. I want to do it in Power Query, so I can then unpivot the date

In SQL I would do something like the following to join the tables and then select what columns I wanted to show

Select type, Value, DisplayValue, Code

From Table1

Inner join Table2 on Table1.type=Table2.value and Table2.Code = 'Code1'

I dont see any option in Power Query to merge tables on conditions.

I am hoping someone has come across this before and can offer a solution

Thanks

[edhans · Accepted solution]

@sprotson The way to do this is to

1. Filter your first table for the the condition you want.
2. Then merge to the other table.

If you don't want your first table permenantly filtered this way, create a reference to it, then do the merge with your 2nd table to the referenced query.

If you would like more help, please give us some actual data so we can work through the code and show you what to do.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[sprotson]

Thanks for the advice - that is actually the first thing I tried before I posted, I got it to work, but was hoping there was maybe a more elegant solution using built in functionality, formulas or code

Table 2 is likly to have approximately 100 + codes and I think that maybe 10 of these can possibly be in table 1.

In my example I said that the join would be on table1.type=table2.value and table2.code= 'code1', but there will be further joins, such as table1.state=table2.value and table2.code= 'code2'.

This means that I will need create 10 duplicates or reference tables, filter each differently and then join each to table 1. This is really the same way I would do it in sql, but was hoping I could somehow pull the data back into new columns with one action based on multiple conditions in the joins to save having so many tables - but hey ho

The sql solution with 2 codes would be

SELECT App.name, App.type, LT1.DisplayValue, App.state, LT2.DisplayValue AS State
FROM dbo.Application AS App INNER JOIN
dbo.LookupTable AS LT1 ON App.type = LT1.Value AND LT1.Code = 'AppType' INNER JOIN
dbo.LookupTable AS LT2 ON App.state = LT2.Value AND LT2.Code = 'TrueFalse'

I appreciate your prompt response and solution.

[edhans]

If it works that may be the solution. It is built in You cannot think of SQL code and go "ok, how do I implement this code in Power Query." Power Query works a lot like SQL, but it isn't SQL and as a primary transformational tool, it has different features. 

Again, if you can provide data, we can help further. 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[sprotson]

I approciate that I cant keep thinking about how to do it in SQL, but essentially the Merge/Filter solution is the same as it would be done in excel, so thinking that way has served me well in terms of this scenario

The reason I asked the question was to try to get a solution that was cleaner and not like sql

Below is some sample data with 2 codes, if you have the time to look further

App Table

Name	Type	State
App1	1	0
App2	1	0
App3	2	1
App4	2	1
App5	3	1

LookupTable

Value	DisplayValue	Codeset
1	DB	AppType
2	Server	AppType
3	Desktop	AppType
0	TRUE	TrueFalse
1	FALSE	TrueFalse

Results I would like

Name	Type	State
App1	DB	TRUE
App2	DB	TRUE
App3	Server	FALSE
App4	Server	FALSE
App5	Desktop	FALSE

Cheers

[edhans]

Hey @sprotson , this is interesting. I've never thought of trying this. Here is the code from a Join - this left outer, but inner, anti, whatever, this is the basic formula. The #"Lookup Table" parameter is a reference to the Lookup Table. That is what you are joining.

Table.NestedJoin(#"Changed Type", {"Type"}, #"Lookup Table", {"Value"}, "Lookup Table", JoinKind.LeftOuter)

  So what if we replaced that with a Table.SelectRows?

#"Merged Queries" = 
        Table.NestedJoin(
            #"Changed Type", 
            {"Type"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "AppType"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        )

Now it only merges data from the Lookup table if Codeset is AppType.

I am not sure I followed your need start to finish as some of your column names changed. You had Type in one table, Value in another, etc.

But doing a regular merge (top function) returns this, which is not what you want.

My modified code though returns this:

Codesets TRUE/FALSE are not in this.

So, I don't think this is any different in the background as to how I recommended in the first place, but it is now on one line of code and you do not need a reference query to refer to. 

But, you do have to manually edit the M code to do this. It cannot be done through the UI tools.

Let me know if that is a bit more on point for you.

[sprotson]

Thanks - that works perfectly when I do the merge once - for example on type=value & codeset = 'AppType'

As soon as I then try to do another merge to the lookup table, this time on state=value & codeset = 'TrueFalse'. then I lose the previous DisplayValue (when I expand the lookup table columns) from the first merge. even if I do Merge as New.

I guess you can only merge to the same table once

I think I will just go with the duplicate/reference lookup table methodology

Appreciate all your help - cheers

[edhans]

It works fine. I suspect your final "in" statement called the wrong step.

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", Int64.Type}, {"State", Int64.Type}}),
    #"Merged Queries" = 
        Table.NestedJoin(
            #"Changed Type", 
            {"Type"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "AppType"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table" = Table.ExpandTableColumn(#"Merged Queries", "Lookup Table", {"DisplayValue"}, {"DisplayValue"}),
    #"Merged Queries1" = 
        Table.NestedJoin(
            #"Expanded Lookup Table", 
            {"State"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "TrueFalse"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Lookup Table", {"DisplayValue"}, {"DisplayValue.1"})
in
    #"Expanded Lookup Table1"

You can do as many of these as you like. Nothing from a previous step will be lost.

[sprotson]

Thanks for your response again, but I am not sure I am following

As I said, I am a complete newbie, so please excuse my ignorance

The process I am following is to use the UI function Merge as New with the following tables - Application_Versions with LookupTable on type = value.

This creates a new query called "Merge1"

I then adjust the what is in the formual bar with the following to make the join conditional

= Table.NestedJoin(
#"Application_Versions",
{"type"},
Table.SelectRows(#"LookupTable", each [CodeSet] = "AppType"),
{"Value"},
"LookupTable",
JoinKind.LeftOuter
)

If I then look at the advanced editor, it has the following

let
Source =
Table.NestedJoin(
#"Application_Versions",
{"type"},
Table.SelectRows(#"LookupTable", each [CodeSet] = "AppType"),
{"Value"},
"LookupTable",
JoinKind.LeftOuter
)
in
Source

I can then decide which columns from LookupTable to include in the new query.

The next step I follow is to do another merge, this time Merge1 with LookupTable on state=value. I would then adjust like before to add the codeset condition into the formula as such

#"Application_Versions",
{"state"},
Table.SelectRows(#"LookupTable", each [CodeSet] = "TrueFalse"),
{"Value"},
"LookupTable",
JoinKind.LeftOuter
)

Before I adjust the forumal I can see the columns from LookuTable previosuly selected, but after adjustment they are gone. This makes sense  as you surely wouldnt be able to select the same column from the same table again

I am not sure where your code goes, at what point is it used in my process or if I need to do any of the steps in my process

Cheers

[edhans]

Show me the full query M code, and use the code box if possible - the </> icon.

But I think the issue is the last two lines in your full query are:

in
   Source

Should be:

in
    #"Application_Versions"

but I'd need to see the full M query to be sure. I'm only seeing snippits.

EDIT: Actually, nevermind. Your issue is this:

Table.NestedJoin(
#"Application_Versions",

but I cannot tell you how to fix it without seeing the full M code.

[sprotson]

Sorry, I really appreciate your help but I am just not following what you are asking for, I have sent all code that I have used, not just snippets

After doing the initial merge, without conditions, the following is pasted into the formula bar to add the condition

= Table.NestedJoin(
            #"Application_Versions", 
            {"state"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "TrueFalse"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        )

At this point the advanced editor has the following

let
    Source     = 
        Table.NestedJoin(
            #"Application_Versions", 
            {"state"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "TrueFalse"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        )
in
    Source

I then expand the columns of the merged table to pick DisplayValue

I then go ahead and do another merge without conditions and once complete, paste in the adjusted code for apptype codeset

= Table.NestedJoin(
            #"Application_Versions", 
            {"type"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "AppType"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        )

I am presuming you are suggesting that the first merge should be adjusted to include the 2nd condition, rather than doing a 2nd merge on the new condition.

I dont follow the code you sent or where you are suggesting this should go, or in fact what point in the process is it used (if at all). ie do I do a normal merge and then paste over with that code (like I have been doing, but instead with 2 conditions), or is it something else I should be doing?

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", Int64.Type}, {"State", Int64.Type}}),
    #"Merged Queries" = 
        Table.NestedJoin(
            #"Changed Type", 
            {"Type"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "AppType"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table" = Table.ExpandTableColumn(#"Merged Queries", "Lookup Table", {"DisplayValue"}, {"DisplayValue"}),
    #"Merged Queries1" = 
        Table.NestedJoin(
            #"Expanded Lookup Table", 
            {"State"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "TrueFalse"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Lookup Table", {"DisplayValue"}, {"DisplayValue.1"})
in
    #"Expanded Lookup Table1"

Your code above

[edhans]

Let's back up a second @sprotson 😁

Let me post my full M code here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoMFTSUQJhA6VYHbCAEbqAMZADEYQKmKALmAI5xhCBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", Int64.Type}, {"State", Int64.Type}}),
    #"Merged Queries" = 
        Table.NestedJoin(
            #"Changed Type", 
            {"Type"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "AppType"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table" = Table.ExpandTableColumn(#"Merged Queries", "Lookup Table", {"DisplayValue"}, {"DisplayValue"}),
    #"Merged Queries1" = 
        Table.NestedJoin(
            #"Expanded Lookup Table", 
            {"State"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "TrueFalse"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Lookup Table", {"DisplayValue"}, {"DisplayValue.1"})
in
    #"Expanded Lookup Table1"

ANd the code for the Lookup Table (the above returns errors until both tables are in Power Query)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJxAhKOBQUhlQWpSrE60UpGQH5walFZahGahDFIdWpxdkl+AZqMAZAfEhTqCqKKSlPdEnOKIRIgC9wcfYLRZGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, DisplayValue = _t, Codeset = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}})
in
    #"Changed Type"

So above, the steps are as follows:

1. Source and Changed Type are just me pasting data into the model. You would have a real data source there and may or may not have any Changed Type. SQL Server doesn't require this for example.
2. Merged Queries is the first merge. I did this through the UI with no conditions. THen, I edited the code to remove the reference to just the #"Lookup Table" and replaced it with a filtered table using Table.SelectRows(#"Lookup Table", each [Codeset] = "AppType")
3. Then I expanded the column I wanted, DisplayValue.
4. Then I created another merge through the UI and it built the formula for me. Again, I got rid of the #"lookup Table" refrence and replaced that with another filtered table - Table.SelectRows(#"Lookup Table", each [Codeset] = "TrueFalse")
5. Then I expanded the DisplayColumns column again.

Nothing gets lost. it keeps adding the columns. 

If you are seeing things go missing, it is likely because you are editing the code manually and not taking the first parameter of all Table.XXXX() functions into account. If you replaced my 2nd Merged Queries above with this:

    #"Merged Queries1" = 
        Table.NestedJoin(
            #"Changed Type", 
            {"State"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "TrueFalse"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        )

it would be going back to the #Changed Type step skipping the first Merged Queries step, so in that case, the first merge will disappear from your results, becuse you jumped back to an earlier table. #Changed Steps is not the name of a step, it is the name of the table that you changed the data types of. Merged Queries is not the name of a step, it is the name of a table that has a merge operation in it. 

Note that "step names" can also be lists, records, and scalar values depending on what you are doing, but they are always an object of some sort.

Does that help?

[sprotson]

I really appreciate all your help and especially the time you have spent on this, but I simply dont follow the steps you are taking and more specifically the exact code that you are typing in on each step.

Is it possible for you to do a short screen record to show me the process as I am not getting this, and details exactly the code you are etering in each step.

I think seeing it visually would help at lot

If you are not able to do this, and I certainy understand it is a big ask, then I think I will probably go with the option of duplicating the lookup table, filtering and merging multiple times

Thanks

[sprotson]

I think this might be my M code after doing the second merge and then adjusteing the formula

// Application_Versions
let
    Source = Table.NestedJoin(ApplicationCME, {"Key"}, ConfigurationItemVersion, {"Key"}, "ConfigurationItemVersion", JoinKind.Inner),
    #"Expanded ConfigurationItemVersion" = Table.ExpandTableColumn(Source, "ConfigurationItemVersion", {"AddedDate", "UpdatedBy", "IsDeleted"}, {"ConfigurationItemVersion.AddedDate", "ConfigurationItemVersion.UpdatedBy", "ConfigurationItemVersion.IsDeleted"})
in
    #"Expanded ConfigurationItemVersion"

// LookupTable
let
    Source = Sql.Database("77.74.194.165,49000", "GK_Test_649_Spotlight_Demo1", [CreateNavigationProperties=false]),
    dbo_LookupTable = Source{[Schema="dbo",Item="LookupTable"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_LookupTable, each true)
in
    #"Filtered Rows"

// Merge1
let
    Source = Table.NestedJoin(
            #"Application_Versions", 
            {"type"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "AppType"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        ),
    #"Expanded LookupTable" = Table.ExpandTableColumn(Source, "LookupTable", {"DisplayValue"}, {"LookupTable.DisplayValue"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded LookupTable",{{"LookupTable.DisplayValue", "AppType"}}),
    #"Merged Queries" = Table.NestedJoin(
            #"Application_Versions", 
            {"state"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "TrueFalse"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        ),
    #"Expanded LookupTable1" = Table.ExpandTableColumn(#"Merged Queries", "LookupTable", {"DisplayValue"}, {"LookupTable.DisplayValue"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded LookupTable1",{{"LookupTable.DisplayValue", "StateEnabled"}})
in
    #"Renamed Columns1"

[edhans]

Use this M code for Merge 1.

// Merge1
let
    Source = Table.NestedJoin(
            #"Application_Versions", 
            {"type"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "AppType"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        ),
    #"Expanded LookupTable" = Table.ExpandTableColumn(Source, "LookupTable", {"DisplayValue"}, {"LookupTable.DisplayValue"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded LookupTable",{{"LookupTable.DisplayValue", "AppType"}}),
    #"Merged Queries" = Table.NestedJoin(
            #"Renamed Columns", 
            {"state"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "TrueFalse"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        ),
    #"Expanded LookupTable1" = Table.ExpandTableColumn(#"Merged Queries", "LookupTable", {"DisplayValue"}, {"LookupTable.DisplayValue"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded LookupTable1",{{"LookupTable.DisplayValue", "StateEnabled"}})
in
    #"Renamed Columns1"

Both of your Table.NestedJoin() functions were using #"Application Versions" as the first table. So the 2nd join was ignoring the first. Your 2nd join needs to be based on the "Renamed Columns" table - the step right above it.

[sprotson]

Thanks - that makes sense now

Each time I do a new merge (to add 1 more column), I need to Merge as New with the formula reference being to the previous merged table. That way the previously added column (from the previosu merge) remains

Its a solution using formulas rather than filtering, but it still means having multiple Merge tables, as opposed to multiple LookUp tables (filtered). Lets say I have 10 distinct lookup codes, so I would have to do 10 merges - which gives the same number of queries as when I have 10 seperate filtered look up tables.

I think I may go for the original solution, as I think it is easier to manage, and actually the filtered lookup tables can then be merged with other tables in my model if required - as some of the codes are used elsewhere

Ideally, it would be good to have a formula that allows me to do 1 merge and add multiple columns all from the same table - ie AppType, State etc from a single formula/merge

I dont think that is a merge, more like a conditional lookup of some sort

Is there any sort of formula that can be used in a "Custom Column" that would allow me to grab a column from the lookup table based on a condition, and then add another custom column based on another condition?

[edhans]

A few things:

"Each time I do a new merge (to add 1 more column), I need to Merge as New with the formula reference being to the previous merged table. That way the previously added column (from the previosu merge) remains

Its a solution using formulas rather than filtering, but it still means having multiple Merge tables, as opposed to multiple LookUp tables (filtered). Lets say I have 10 distinct lookup codes, so I would have to do 10 merges - which gives the same number of queries as when I have 10 seperate filtered look up tables."

You just merge as normal. Then you edit the code. You are trying to do everything in the advanced editor and that is how your 2nd Merge referenced the original table. Everything in M is formulas. Everything. A filter is the formula using the Table.SelectRows() function. A merge is a formula using the Table.NestedJoin() function.

You can do all 10 "lookup codes" in one merge. Your Table.SelectRows would look like this:

Table.SelectRows(SourceTable, each ([Field1] = 1) and ([Field2] = 2) and ([Field3] = 3))

And so on.

But I agree, if pre-filtering the table for other uses makes more sense to you, do it that way. There is right or wrong, there is better or worse from the standpoint of which is easier to maintain over time, and which one mitigates the possiblity of error better.

I don't know why you think what I posted isn't a merge. It is, with a condition. I have a blog post coming out on this on Sept 8 on my site, but as a spoiler, what I posted folds, and folds as a merge. This is the SQL statement Power Query folds to the SQL Server. It pre-filters the table, then does the merge, in one step. Server does all of the work. It still works for flat files and non-foldable sources as well, it will just be slower because 100% of the work has to be done by Power Query - that is the case no matter which method you use.

You do not, in my opinion, want to go down the path of "lookup columns" in Power Query. It is not efficient and lookups in other tables, 10,000 rows? No problem. 1,000,000 rows? It will never finish. Power Query isn't Excel and VLOOKUP and their equivalents, while possible in Power Query, are generally very inefficient. I avoid them on all but the smallest of models.

''Ideally, it would be good to have a formula that allows me to do 1 merge and add multiple columns all from the same table - ie AppType, State etc from a single formula/merge"

I don't understand this comment at all. You can always expand any/all columns from the merged (2nd) table as long as your merge condtion is the same. You cannot expand column 1 if field 1 is "A" and column 2 if field 2 is "B" - that is two seperate merges as you cannot merged based ([Field 1] = "A") and ([Field 2] = "B") as that logic is not what you want. You are thinking in terms of Excel having a compound formula with a bunch of nested IF() statements, or an IFS() statement. Again, Power Query isn't Excel. You could do that in DAX using a bunch of IF() with LOOKUPVALUE() functions, but that would get slow as well as difficult to maintain. I doubt I'd go down that path. Do data modeling in Power Query or your source system, not in DAX.

[sprotson]

Thanks - I think I will be doing this with multiple lookup tables, each filtered

The reason I need to do multipel merges, is that all the data I want to expose is in the same column of the lookup table. I merge based on 1 condition, expose the column, merge on another condition, then expose the same column again.

Below is a sample of what would be in the lookup table, as you see, all the values I want to retrieve and diaply in different columns are all within the "DisplayValue" column

Value	DisplayValue	Codeset
0	Enabled	TrueFalse
1	Disabled	TrueFalse
2	Chat	AppType
3	Email	AppType
1	TRUE	FalseTrue
0	FALSE	FalseTrue

Here is the ultimate results of doing 3 merges

1 is state=value and codeset - TrueFalse

2 is Type=values and codeset = AppType

3 is Server=value and codeset - FalseTrue

BCID	State	Type	Server	State_Readable	Type_Readable	Server_Readable
1	0	2	1	Enabled	Chat	TRUE
2	0	2	1	Enabled	Chat	TRUE
3	0	3	1	Enabled	Email	TRUE
4	1	3	0	Disabled	Email	FALSE
5	1	3	0	Disabled	Email	FALSE

Columns 2,3 & 4 are from the original query and columns 5,6 & 7 are the exposed DisplayValue columns from merges 1, 2 & 3

I hope that makes sense why I think I need to do multiple merges, as the data is always in the same column each time

As I said previously, having multiple lookup tables (each filtered) is similar to what I would need to do in sql - so I will be going with that methodologu

Thanks for your help

[sprotson]

Any thoughts on if I could use a cusotm column to retrieve the data rather than a merge?

I really dont know anything about M Code, so am pretty clueless where to start

[amitchandak]

@sprotson , In power query you have to merge and then remove or do some operation.

refer

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

https://www.myonlinetraininghub.com/excel-power-query-vlookup

[Greg_Deckler]

@sprotson - Not sure about the Power Query although seems like a Table.Join situation @ImkeF @edhans 

In DAX you would just use LOOKUPVALUE or MAXX(FILTER(...),...)