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 
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?

" 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.

     

4 Replies

    • 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
        Super 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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    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.