Forum Discussion

BhaskarBalusani's avatar
BhaskarBalusani
New Member
1 year ago
Solved

Filter based on multiple slicer values

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  ...
" Slicer"
"DAX "
  • Rupak_bi's avatar
    Rupak_bi
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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.

     

BhaskarBalusani's avatar
BhaskarBalusani
New Member
1 year ago

Table 1:

ID NameValue
1AsgMinSize1
1AsgMaxSize10
2AsgMinSize2
2AsgMaxSize20
3AsgMinSize3
3AsgMaxSize30

 

Table 2:

ID CombinedValue
1AsgMinSize:1,AsgMaxSize:10
2AsgMinSize:2,AsgMaxSize:20
3AsgMinSize: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's avatar
Rupak_bi
Icon for Super User rankSuper User
1 year ago

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.