filter table based on selected value using contains

[HIMANSHU_SINGH]

Hi,

I am trying to filter the table in which column of the table contains the selected value(like substring). I am getting the selected value in the card when I select a bin of bar charts and using the same selected value to filter the table but I am getting blank rows.

Here's the bar chart by setting up the filter of Type to T1:

Here's the table data:

so if I click any of the bars of the above chart(e.g clicking "A"), I should get the below results:

I am trying to achieve this by using the below measure:
If(CONTAINS(MAX(category_table[Category]),SELECTEDVALUE(category_table[Category])),1,0)

After this, I'll set this measure to 1 to filter out the table.
Let me what I need to do here or what I am doing wrong.

@amitchandak 

@AllisonKennedy

@ibarrau 

[v-zhangti]

Hi, @HIMANSHU_SINGH 

Please check the following methods.

Measure = IFERROR(SEARCH(SELECTEDVALUE(Category[Category]),SELECTEDVALUE('Table'[Category])),-1)

Put Measure in Fliters and set not equal to -1.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HIMANSHU_SINGH]

Hi @v-zhangti 

Thank you for your reply. But I don't have any category table. 

Is it possible to create the category table on the run and then use the above measure?

[v-zhangti]

Hi, @HIMANSHU_SINGH 

So where does your A B C D come from?

Best Regards

[HIMANSHU_SINGH]

Here's my main sample dataset:

I am using this to create a chart by setting up the Type filter to T1.

[v-zhangti]

Hi, @HIMANSHU_SINGH 

You try the following methods.

Table:

Table 2 = CALCULATETABLE( VALUES('Table'[Category]),FILTER('Table',[Type]="T1"))

Measure = IFERROR(SEARCH(SELECTEDVALUE('Table 2'[Category]),SELECTEDVALUE('Table'[Category])),-1)

Please check whether the result of this time meets your expectation?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HIMANSHU_SINGH]

Hi @v-zhangti ,

Thanks for your solution. It's near to complete. I want to show these categories in the bar chart along with their values as I mentioned in the post.

[v-zhangti · Accepted solution]

Hi, @HIMANSHU_SINGH 

You can try the following methods.

Table 2 = 
CALCULATETABLE( SUMMARIZE('Table','Table'[Category],'Table'[Value]),FILTER('Table',[Type]="T1"))

The view on the left is obtained from Table 2. Other settings remain unchanged.

This is the relevant document, hope to help you:

https://docs.microsoft.com/dax/search-function-dax#example-error-handling-with-search 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

In the Data Table itself, you should have a Master Category column with values of A,B,C and D.  Now create your visual/slicers.

[HIMANSHU_SINGH]

@Ashish_Mathur I don't have the Master Column with values of A,B,C,D.

[Ashish_Mathur]

So if they are in another table then one can always bring them over into your table by using the RELATED() or LOOKUPVALUE() funcion.

[HIMANSHU_SINGH]

Yes.

[lbendlin]

Is the data source for your column chart a disconnected table?

[HIMANSHU_SINGH]

I am using the same data source for both tables.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome based on the sample data you provided.