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...
  • smpa01's avatar
    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