Forum Discussion
Anonymous
4 years agoNot applicable
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...
- 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
smpa01
Community Champion
4 years agoAnonymous 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