How can I filter a table by two columns in Power BI

[carloswalder]

Hi,

I have a table of order quantities, which has a date column (format: date) and a country column (text).

I would like to filter this column by not using e.g. orders from US in second quarter of 2021.

In the normal filtering I either can say "not US", but then filters also the orders from US outside from second quarter in 2021, or can say "not Q2 in 2021" then it filters all countries, but not only US!

Many thanks, maybe this is a simple question, but I am driving crazy now! 

[#2]

@carloswalder 

I suggest advanced filter. In the attached sheet you can execute advanced filter by clicking cell E5. In cells E8 and E9 you can enter the country and month you want to exclude. The criteria range is E1:E2 and cells E2 holds the rule for the advanced filter.

[#3]

@carloswalder 

=FILTER(A2:D22,(C2:C22<>"US")+((C2:C22="US")*(D2:D22<>8)))

If you work with Office365 or 2021 you can apply FILTER function as well.

[#4 · Accepted solution]

I would suggest creating a helper column with a formula like:
="Q"&INT(MONTH([Date])/4)+1 & "-" & YEAR([Date]) & "-" & [Country]
Then filter out "Q2-2021-US"

[#5]

@mtarler 

This was the right hint, as I need to do this in power query, my formular now looks like this - this of course can be optimized:

#"Extracted Year" = Table.TransformColumns(#"Added Custom",{{"Quarter", Date.Year, Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Extracted Year",{{"Quarter", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Quarter", "Year"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Quarter", each [WKOrderIntakeDate]),
#"Calculated Quarter" = Table.TransformColumns(#"Added Custom1",{{"Quarter", Date.QuarterOfYear, Int64.Type}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Calculated Quarter",{{"Quarter", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "BU", each [WKBusinessUnit]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"BU", type text}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type3", "BU & QUARTER", each "Q" & [Quarter] & "BU" & [BU] & "Y" & [Year]),
#"Filtered Rows5" = Table.SelectRows(#"Added Custom3", each [#"BU & QUARTER"] <> "Q2BU3Y2021")
in
#"Filtered Rows5"

[#6]

@carloswalder 

In general you may create column for filtering at once

...

    #"Changed Type" = Table.TransformColumnTypes(
        PrevStep,
        {
            {"Date", type date},
            {"WKBusinessUnit", type text}
        }),
    #"Inserted Filter" = Table.AddColumn(
        #"Changed Type",
        "Filter",
        each "Q" & Text.From( Date.QuarterOfYear([Date]) ) &
             "BU" & [WKBusinessUnit] &
             "Y" & Text.From( Date.Year( [Date] )),
        type text)
in
    #"Inserted Filter"

and apply to it filter with parameter like Q2BU3Y2021