Forum Discussion
How can I filter a table by two columns in Power BI
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!
- 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 Replies
=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.
- 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"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"
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.
