Power BI仪表板，其中核心过滤条件与数字字段相分离 [英] Power BI Dashboard where the core filter condition is a disjunction on numeric fields

问题描述

我们正在尝试实现一个仪表板，该仪表板显示各种表，指标和一张地图，其中数据集是客户列表。主要过滤条件是两个数字字段的和。我们希望用户能够选择 [字段1] 的阈值和 [字段2] ，然后加上条件 [字段1]> =< threshold> OR [field 2]> =< threshold> 。

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自然在所有过滤器之间强加 AND ，而且没有一种整齐的方法指定 OR 。您能否建议一种使用两个数字字段定义计算的方法，然后将其用作同一交互式仪表板屏幕中的过滤器？或者，是否有一种方法可以在显示仪表板之前先提示用户输入两个阈值-因此，当他们在该参数设置屏幕上单击提交时，

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?

为回应评论而添加：

数据可能非常简单：那里没有复杂性。

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

假设数据是客户列表，其中包含客户ID，国家/地区，性别，最近12个月内的购买次数，以及最近12个月内的购买次数。我希望最终用户（没有任何技术技能）为总价值（例如$ 1,000）和购买次数（例如10）指定最低阈值，然后将数据集限制为最近12个月内交易总价值的数据> $ 1,000或最近12个月内的购买次数> 10。

Suppose the data was a list of customers with customer id, country, gender, total value of transactions in the last 12 months, and 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).

推荐答案

此处的键

假设我们有以下 Sales 表：

Customer  Value  Number
-----------------------
A         568     2
B         2451   12
C         1352    9
D         876     6
E         993    11
F         2208   20
G         1612    4

然后我们将创建两个新表用作参数。您可以做一个计算表，例如

Then we'll create two new tables to use as parameters. You could do a calculated table like

Number = VALUES(Sales[Number])

或更复杂的东西

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

或使用Enter Data或其他方式手动定义表。

Or define the table manually using Enter Data or some other way.

无论如何，一旦有了这些表，将其列命名为您想要的列（我使用了 MinNumber 和 MinValue ）并编写了过滤指标

In any case, once you have these tables, name their columns what you want (I used MinNumber and MinValue) and write your filtering measure

Filter = IF(MAX(Sales[Number]) > MIN(Number[MinCount]) ||
            MAX(Sales[Value])  > MIN('Value'[MinValue]),
            1, 0)

然后将您的 Filter 度量作为可视级过滤器，其中 Filter 并非 0 并使用 MinCount 和 MinValues 列作为切片器。

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

如果您选择10 MinCount 和 1000 的 MinValue ，则您的表格应类似于

If you select 10 for MinCount and 1000 for MinValue then your table should look like this:

请注意 E 和 G 仅超过阈值之一，并且 A 和 D 被排除。

Notice that E and G only exceed one of the thresholds and tha A and D are excluded.

这篇关于Power BI仪表板，其中核心过滤条件与数字字段相分离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！