Filter table with multiple logical conditions dynamically

[Anonymous]

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!

[Sahir_Maharaj · Accepted solution]

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 · Accepted solution]

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. 

[Sahir_Maharaj · Accepted solution]

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.

[rajendraongole1]

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]

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.