Filter based on multiple slicer values

[BhaskarBalusani]

I have a two table with below schema

Table 1
ID | Name | Value|

In Table 1 each ID can have multiple Name and value pairs.

So I have combined all Name & Values pairs in Table 2.
Table 2 
ID | Combined Values

Combined values will look like "name1:values,name2:value2".

Now I need to support multiple filters on names and values, 
first drop down for name1
second drop down for value 1
Third drop down for name2

Fourth drop down for value2,

Now I need to filter my tables based on these four entries. I need to filter ID's where  "name1:values,name2:value2" is present.

Can you please suggest on how we can achieve this?

[Rupak_bi · Accepted solution]

Hi @BhaskarBalusani 

You can achieve this with two slicer as well if you have the combined table and the pattern "AsgMinSize" and "AsgMaxSize" is constant. see below approach

approach....

1. create two numeric field parameter

min range = GENERATESERIES(0, 20, 1)

max range = GENERATESERIES(0, 40, 10)

2. create a measure to filter the table

filter value =

Var Min_value = SELECTEDVALUE('min range'[min range])

Var Max_value = SELECTEDVALUE('max range'[max range])

var filter_id = "AsgMinSize:"&Min_value&",AsgMaxSize:"&Max_value

return

CALCULATE(max('Table (2)'[CombinedValue]),FILTER('Table (2)','Table (2)'[CombinedValue]=filter_id))

Now get the output in a table visual along with ID.

[Anonymous · Accepted solution]

Hi @BhaskarBalusani  , hello Rupak_bi, thank you for your prompt reply!

Please try as following:

1. Create the calculated table for slicers:

Name1Table = DISTINCT('Table1'[Name])
Name2Table = DISTINCT('Table1'[Name])
Value1Table = DISTINCT('Table1'[Value])
Value2Table = DISTINCT('Table1'[Value])

2.Then create a measure to get the SelectedCombineValue:

SelectedCombinedValue = 
VAR Name1Selected = SELECTEDVALUE('Name1Table'[Name])
VAR Value1Selected = SELECTEDVALUE('Value1Table'[Value])
VAR Name2Selected = SELECTEDVALUE('Name2Table'[Name])
VAR Value2Selected = SELECTEDVALUE('Value2Table'[Value])

RETURN Name1Selected & ":" & Value1Selected & "," & Name2Selected & ":" & Value2Selected

3.Later, create another flag measure to filter the visual:

IsMatch = 
IF (
    CONTAINSSTRING(MAX('Table 2'[CombinedValue]), [SelectedCombinedValue]),
    1,
    0
)

Result for your reference:

Best regards,

Joyce

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

[Rupak_bi]

Hi @BhaskarBalusani 

Please share sample data in plain text and the desired output based on sample data. 

[BhaskarBalusani]

Table 1:

ID	Name	Value
1	AsgMinSize	1
1	AsgMaxSize	10
2	AsgMinSize	2
2	AsgMaxSize	20
3	AsgMinSize	3
3	AsgMaxSize	30

Table 2:

ID	CombinedValue
1	AsgMinSize:1,AsgMaxSize:10
2	AsgMinSize:2,AsgMaxSize:20
3	AsgMinSize:3,AsgMaxSize:30

and here are my filters:

Based on these four filters , I need to return only one row where ID is 1.

[Rupak_bi · Accepted solution]

Hi @BhaskarBalusani 

You can achieve this with two slicer as well if you have the combined table and the pattern "AsgMinSize" and "AsgMaxSize" is constant. see below approach

approach....

1. create two numeric field parameter

min range = GENERATESERIES(0, 20, 1)

max range = GENERATESERIES(0, 40, 10)

2. create a measure to filter the table

filter value =

Var Min_value = SELECTEDVALUE('min range'[min range])

Var Max_value = SELECTEDVALUE('max range'[max range])

var filter_id = "AsgMinSize:"&Min_value&",AsgMaxSize:"&Max_value

return

CALCULATE(max('Table (2)'[CombinedValue]),FILTER('Table (2)','Table (2)'[CombinedValue]=filter_id))

Now get the output in a table visual along with ID.