Forum Discussion
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 > [slicerValue] && columnB > [slicerValue]) || (columnA = [slicerValue] && columnC = [slicerValue])
As I´ve mentioned, I want to the user to be able to change the table using a slicer (or any other way of filtering dynamically).
I am a little new to Power Bi and haven´t found a way to do that yet, so if someone can include some images as examples would be great!
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.
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.
4 Replies
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.
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.
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.
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.
