Forum Discussion

carloswalder's avatar
carloswalder
Copper Contributor
Jan 21, 2022
Solved

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 ...
BI & Data Analysis
  • mtarler's avatar
    mtarler
    Jan 21, 2022
    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"
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 21, 2022
    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"
    • carloswalder's avatar
      carloswalder
      Copper Contributor
      Jan 24, 2022

      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"

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 24, 2022

        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

Resources