Power BI 仪表板，其中核心筛选条件是数值字段的析取 [英] Power BI Dashboard where the core filter condition is a disjunction on numeric fields
问题描述
我们正在尝试实现显示各种表格、指标和地图的仪表板，其中数据集是客户列表.主要过滤条件是两个数字字段的分离.我们希望用户能够选择 [field 1] 的阈值和 [field 2] 的单独阈值，然后施加条件 [field1] >= <阈值>或 [字段 2] >= <阈值>.
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 的简洁方法.您能否建议一种使用两个数字字段定义计算的方法，然后将其用作同一交互式仪表板屏幕中的过滤器?或者，有没有办法在显示仪表板之前首先提示用户输入两个阈值 - 所以当他们在该参数设置屏幕上单击 Submit 时，他们会被带到主仪表板屏幕已经应用了析取?
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).
推荐答案
这里的关键是创建单独的参数表并使用度量组合条件.
The key here is to create separate parameter tables and combine conditions using a measure.
假设我们有以下 Sales 表:
Suppose we have the 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
然后我们将创建两个新表用作参数.你可以做一个计算表，如
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)
或者使用输入数据或其他方式手动定义表格.
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.
如果您为 MinCount 选择 10，为 MinValue 选择 1000，那么您的表格应如下所示:
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屋！
