Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

filter rows by using the "AND" Operator

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

 

 

 

 

Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    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

     

     

     

     

     

2 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    4 years ago

    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

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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