Forum Discussion
Containsstring and filter other tables
Hi Rene83
Thanks for your reply.
>>If I click on the colour green in the table with the colours, then the respective entries should be filtered in the table Iphone and Status.
To get the above results, the method I mentioned earlier (changing the structure of the table) is the easiest way. Of course, the another way is to use visual level filter, but this will be a bit more complicated.
Let's take the scenario you're talking about as an example. When you click on green then the respective entries should be filtered in the table Iphone and Status,
(1) create the measures,
selected1 = IF( ISBLANK( SELECTEDVALUE(Colour[Title])),"",SELECTEDVALUE(Colour[Title]))to get the value selected in color table.
(2) Put the following filter measures into their respective visual filter panes
filter2 = var _iphone= FILTER(Reporting, CONTAINSSTRING( Reporting[Iphone],MIN(Iphone[Title]))) var _color=FILTER(Reporting,CONTAINSSTRING(Reporting[Colour],[selected1])) var _table=INTERSECT(_iphone,_color) var _count= CALCULATE( COUNTROWS(Reporting), INTERSECT(_iphone,_color)) return _countfilter3 = var _color=FILTER(Reporting,CONTAINSSTRING(Reporting[Colour],[selected1])) var _status=FILTER(Reporting, CONTAINSSTRING( Reporting[Status],MIN('Status'[Title]))) var _count= CALCULATE( COUNTROWS(Reporting), INTERSECT(_color,_status)) return _countresult
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi Rene83
Thanks for reaching out to us.
>>But now I want that when I filter a topic, the other topics are also filtered.
You need to convert the table to the following structure first
To get the table, create a blank query and copy the code below into Advanced Editor,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLjG0VoCyjOAsYyUdJceU3Mw8QyDDqbQ4My+1uNhYKVYHoQumAqgJTIO0oCiwRjILyRRDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Admin = _t, Business = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Admin", type text}, {"Business", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Product", "Product - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Product", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Product.1", "Product.2", "Product.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product.1", type text}, {"Product.2", type text}, {"Product.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Admin", "Business", "Product - Copy"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns"," ","",Replacer.ReplaceText,{"Value"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Attribute"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Admin", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Admin.1", "Admin.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Admin.1", type text}, {"Admin.2", type text}}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type2", {"Business", "Product - Copy", "Value"}, "Attribute", "Value.1"),
#"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Columns1"," ","",Replacer.ReplaceText,{"Attribute", "Value.1"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value1",{"Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Product - Copy", "Value", "Value.1", "Business"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Value.1", "Admin"}, {"Value", "Product"}})
in
#"Renamed Columns2"then put the columns in this table into slicers.
Result:
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi v-xiaotang ,
thank you for your answer.
I have created an example to better explain my problem.
If I click on the colour green in the table with the colours, then the respective entries should be filtered in the table Iphone and Status.
I have uploaded the example file here PowerBi_Sharing
Hi Rene83
Thanks for your reply.
>>If I click on the colour green in the table with the colours, then the respective entries should be filtered in the table Iphone and Status.
To get the above results, the method I mentioned earlier (changing the structure of the table) is the easiest way. Of course, the another way is to use visual level filter, but this will be a bit more complicated.
Let's take the scenario you're talking about as an example. When you click on green then the respective entries should be filtered in the table Iphone and Status,
(1) create the measures,
selected1 = IF( ISBLANK( SELECTEDVALUE(Colour[Title])),"",SELECTEDVALUE(Colour[Title]))to get the value selected in color table.
(2) Put the following filter measures into their respective visual filter panes
filter2 = var _iphone= FILTER(Reporting, CONTAINSSTRING( Reporting[Iphone],MIN(Iphone[Title]))) var _color=FILTER(Reporting,CONTAINSSTRING(Reporting[Colour],[selected1])) var _table=INTERSECT(_iphone,_color) var _count= CALCULATE( COUNTROWS(Reporting), INTERSECT(_iphone,_color)) return _countfilter3 = var _color=FILTER(Reporting,CONTAINSSTRING(Reporting[Colour],[selected1])) var _status=FILTER(Reporting, CONTAINSSTRING( Reporting[Status],MIN('Status'[Title]))) var _count= CALCULATE( COUNTROWS(Reporting), INTERSECT(_color,_status)) return _countresult
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.