Forum Discussion

aa_KF's avatar
aa_KF
Helper I
7 years ago
Solved

Filtering a column with delimiter

Hello, I have a basic table (T1) as shown on the left, I want to be able to filter the rows based on the three available values: A, B, and C. For example if I choose "C" from the filter the table will only show rows with ID 1,2,3 only. 

I have imported the table(T2) and created another table that contain all the possible valuse:  (I used Col2 as the value of the filter)

I then duplicated T1 in order to seperate the values into seperate rows as shown in T3:

I want to show the table without duplicate rows as in T3 but I want to be able to link it somehow with T1 and T2, and then use the filter. 

 

Appreciate your help and time!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

     

    It is not possible. But I could suggest you to modify the measure so that directly you can refer measure instead of the column. But there must be atleast a column from the table which you are searching.

     

    Try this formula,

    Filter Found Measure3 =
    var text_to_find = SELECTEDVALUE(Table2[Column1])
    var text_to_search = MAX(Table3[Column2])
    VAR FIND_RES = FIND(text_to_find,text_to_search,1,BLANK())
    return IF(FIND_RES >= 1, text_to_search)
     
    Now you no need to worry about hiding.
     
    Let me know if it is making sense.
    Thanks

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    aa_KF  - 

    You can create a new Measure like this:

    Filter Found Measure = 
var text_to_find = SELECTEDVALUE('YourFilterTable'[FilterColumn])
var text_to_search = MAX('YourDataTable'[DataColumn])
return FIND(text_to_find,text_to_search,1,BLANK())

    Then add this measure to your table. Those values that do not contain the search text will result in BLANK. Rows with only blank measures are, by default, excluded from the result set. You can make the column very narrow so that it won't be displayed.

    Hope this helps,

    Nathan

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous ,

       

      That was a good idea.

      I would like to suggest creating measure which returns the original string instead of positions like this.

       

      Filter Found Measure =
      var text_to_find = SELECTEDVALUE(Table_to_be_searched[Column_to_be_searched])
      var text_to_search = MAX('Search_table'[Search_column])
      var FIND_RES = FIND(text_to_find,text_to_search,1,BLANK())
      return IF(FIND_RES >= 1, text_to_search)
       
      And you can direclty refer to measure in visual.
      Hope this hepls,
      Thanks.
    • aa_KF's avatar
      aa_KF
      Helper I
      7 years ago

      Thank you Anonymous  for your help I appreicate it, would you please elaborate more?

      I understand that I won't need the third table (where I split up the table values into seperate columns). and by adding new measure to my basic table I'll be able to use as the value filter?

       

      Thank you!

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        aa_KF 

        That's correct -

        You don't need the third table.

        You can have one table that will contain your different filtering possibilities and another table with the text that you want to analyze. It doesn't matter where you create the measure, in terms of functionality, but it would make the most intuitive sense to include it in the table that contains the text like "ABC".

        Then, add the new measure to the table visual to cause the filter affect.

        Does it make sense?

        Nathan