Containsstring and filter other tables

[Rene83]

Hello Experts,

I have a reporting table where the topics are stored in the respective column separated by a semicolon.

Table_Reporting
Product	Admin	Business
Product1; Product2; Product3	Admin1	Business3
Product1	Admin2; Admin3
Product1;Product3		Business1

In further tables the different topics are created in order to make a count.

Table_Product

Topic

Product1

Product2

Product3

Table_Admin

Topic

Admin1

Admin2

Admin3

Table_Business

Topic

Business1

Business2

Business3

With the measurement "count_Product" I count the entries in the respective reporting column. 

count_Product =
VAR _name =
MAX (Table_Product[Topic])
RETURN
CALCULATE (
COUNTROWS ('Table_Reporting'),
CONTAINSSTRING ( 'Table_Reporting'[Product], _name )
)

This all works as well. But now I want that when I filter a topic, the other topics are also filtered.

Thank you

[v-xiaotang]

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.

[Rene83]

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

[v-xiaotang · Accepted solution]

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 _count

filter3 = 
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 _count

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.