Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Filter table with multiple logical conditions dynamically

Hi everyone! I want to create a new table in which I want the user to be able to change its filter dynamically, but the filter should works according to the following logic:     (columnA > [slice...
Need Help
Show and Tell
Tutorial Requests
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello Anonymous,

     

    Can you please try this approach:

     

    1. Create a table for the slicer

    SlicerTable = DISTINCT(financial[ColumnA])

    2. Create a measure that applies your filtering logic dynamically

    DynamicFilter = 
VAR SelectedValue = SELECTEDVALUE(SlicerTable[ColumnA])
RETURN
IF(
    (MAX(financial[ColumnA]) > SelectedValue && MAX(financial[ColumnB]) > SelectedValue) ||
    (MAX(financial[ColumnA]) = SelectedValue && MAX(financial[ColumnC]) = SelectedValue),
    1,
    0
)

    Hope this helps.

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    I want to acknowledge valuable input provided by Sahir_Maharaj  and rajendraongole1  . Their initial ideas help guide my approach. However, I noticed that more details are needed to fully understand this issue.

     

    It is not possible to get the parameters of the slicer in the calculation table, this is due to the design, so I recommend that you create a table visual object for presenting the data.

    (1) Create a slicer table.

    SlicerValues = GENERATESERIES(1, 100, 1)

    (2) Create a measure.

    Flag = 
VAR SelectedValue = SELECTEDVALUE(SlicerValues[Value])
RETURN
IF(
    (MAX(financial[ColumnA]) > SelectedValue && MAX(financial[ColumnB]) > SelectedValue) ||
    (MAX(financial[ColumnA]) = SelectedValue && MAX(financial[ColumnC]) = SelectedValue),
    1,
    0
)

    (3) Create a form visual object and set up filtering on the visual object.

     

    Best Regards,

    Neeko Tang

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

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

Hi Anonymous  - First you can create a disconnected table with below logic

 

SlicerValues = GENERATESERIES(1, 100, 1)

 

create another calculated table, using the following DAX  and call the slicervalue in selectedvalue function.

FilteredTable =
VAR SelectedValue = SELECTEDVALUE(SlicerValues[Value])
RETURN
FILTER(
financial,
(financial[ColumnA] > SelectedValue && financial[ColumnB] > SelectedValue) ||
(financial[ColumnA] = SelectedValue && financial[ColumnC] = SelectedValue)
)

 

 

replace table name as per your mode and columns. 

 

Hope this helps.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I already tried that way but unfortunately it doesn´t work. It seems that when creating a new table, it is not possible to use SELECTEDVALUE() because, if I set it to a number instead of using SELECTEDVALUE(), it works fine.
    For example, it works:

     

    FilteredTable =
VAR SelectedValue = 2000
RETURN
FILTER(
financial,
(financial[ColumnA] > SelectedValue && financial[ColumnB] > SelectedValue) ||
(financial[ColumnA] = SelectedValue && financial[ColumnC] = SelectedValue)
)

     

    But if I write SELECTEDVALUE() to capture the slicer value, then the table is empty all the time.