filter rows by using the "AND" Operator

[Anonymous]

Hello Community,

I have this table 

I want to filters my rows using the AND operator, for example if I select Manufacturing And Quality, it will show me the name of Malek.

I tried changed the OR by AND in the bar formulabut it doesn't work 

Thanks in advance

[smpa01 · Accepted solution]

@Anonymous  I am guessing you want this in PQ. 

Table.SelectRows(
    #"Changed Type",
    each List.Contains(
      List.Intersect(
        {
          List.Distinct(Table.SelectRows(#"Changed Type", each ([entity] = "manufacturing"))[name]),
          List.Distinct(Table.SelectRows(#"Changed Type", each ([entity] = "quality"))[name])
        }
      ),
      [name]
    )
  )

the full code

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45Wyk3MSc1W0gHSeaVpicklpUWZeelKsToImcLSxJzMkkqwWAVWlYlYRZOw6IXzYwE=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [name = _t, entity = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"name", type text}, {"entity", type text}}),
  #"Filtered Rows" = Table.SelectRows(
    #"Changed Type",
    each List.Contains(
      List.Intersect(
        {
          List.Distinct(Table.SelectRows(#"Changed Type", each ([entity] = "manufacturing"))[name]),
          List.Distinct(Table.SelectRows(#"Changed Type", each ([entity] = "quality"))[name])
        }
      ),
      [name]
    )
  )
in
  #"Filtered Rows"

DAX would be more performant for a large table

Table =
VAR _base = { "manufacturing", "quality" }
VAR _count =
    COUNTX ( _base, [Value] )
RETURN
    FILTER (
        tbl,
        tbl[entity]
            IN _base
                && CALCULATE ( DISTINCTCOUNT ( tbl[entity] ), ALLEXCEPT ( tbl, tbl[name] ) ) = _count
    )

from

to

[Pragati11]

Hi @Anonymous ,

I think you will need to use List.Contains in M code. Somthing like below:

= Table.SelectRows(#"Renamed Columns", each List.Contains({'Manufacturing', 'Quality'}, [Entity]))

Just make sure to check column name, all argumets and column values in above code.

Thanks,

Pragati