Power BI Dashboard where the core filter condition is a disjunction on numeric fields

Question

We are trying to implement a dashboard that displays various tables, metrics and a map where the dataset is a list of customers. The primary filter condition is the disjunction of two numeric fields. We want to the user to be able to select a threshold for [field 1] and a separate threshold for [field 2] and then impose the condition [field 1] >= <threshold> OR [field 2] >= <threshold>.

After that, we want to also allow various other interactive slicers so the user can restrict the data further, e.g. by country or account manager.

Power BI naturally imposes AND between all filters and doesn't have a neat way to specify OR. Can you suggest a way to define a calculation using the two numeric fields that is then applied as a filter within the same interactive dashboard screen? Alternatively, is there a way to first prompt the user for the two threshold values before the dashboard is displayed -- so when they click Submit on that parameter-setting screen they are then taken to the main dashboard screen with the disjunction already applied?

Added in response to a comment:

The data can be quite simple: no complexity there. The complexity is in getting the user interface to enable a disjunction.

Suppose the data was a list of customers with customer id, country, gender, the total value of transactions in the last 12 months, and the number of purchases in last 12 months. I want the end-user (with no technical skills) to specify a minimum threshold for total value (e.g. $1,000) and number of purchases (e.g. 10) and then restrict the data set to those where total value of transactions in the last 12 months > $1,000 OR number of purchases in last 12 months > 10.

After doing that, I want to allow the user to see the data set on a dashboard (e.g. with a table and a graph) and from there select other filters (e.g. gender=male, country=Australia).

Answer 1

• we need to create separate parameter tables and combine the conditions using a measure.Lets say we have this following Sales table:

Customer  Value Number

-----------------------

A         568 2

B         2451 12

C         1352 9

D         876 6

E         993 11

F         2208 20

G         1612 4

• To use as parameters, we'll create two new tables. It is better to do a calculated table like

Number = VALUES(Sales[Number])

• If  you want to make it more complex, do it in this way

Value = GENERATESERIES(0, ROUNDUP(MAX(Sales[Value]),-2), ROUNDUP(MAX(Sales[Value]),-2)/10)

• We can define the table manually by filling the Data or in some other way.

• Once you have these tables, name their columns: “MinNumber” and “MinValue” 

• Filtering measure: 

Filter = IF(MAX(Sales[Number]) > MIN(Number[MinCount]) ||

           MAX(Sales[Value])  > MIN('Value'[MinValue]),

            1, 0)

• Mention your Filter measure as a visual level filter where Filter is not 0 and use MinCount and MinValues column as slicers.

• Let's say, If you select 10 for MinCount and 1000 for MinValue then your table should look like this: