Forum Discussion

Rene83's avatar
Rene83
Frequent Visitor
4 years ago
Solved

Containsstring and filter other tables

Hello Experts,

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

 

Table_Reporting  
   
ProductAdminBusiness
Product1; Product2; Product3Admin1Business3
Product1Admin2; 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

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

3 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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's avatar
      Rene83
      Frequent Visitor
      4 years ago

      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's avatar
        v-xiaotang
        Community Support
        4 years ago

        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.