Power BI：如何在2个不同的过滤器之间使用OR？ [英] Power BI: How to use OR between 2 different filters?

问题描述

我有这样一张桌子：

+------+------+------+
| Lvl1 | Lvl2 | Lvl3 |
+------+------+------+
| A1   | B1   | C1   |
| A1   | B1   | C2   |
| A1   | B2   | C3   |
| A2   | B3   | C4   |
| A2   | B3   | C5   |
| A2   | B4   | C6   |
| A3   | B5   | C7   |
+------+------+------+

其中有点像层次结构。

当用户选择A1时，他实际上选择了前3行，B1，则选择了前2行，

When user select A1, he actually selects the first 3 rows, B1, selects first 2 rows, and C1, selects only the first row.

即A是最高级别，C是最低级别。请注意，来自不同级别的ID是唯一的，因为它们具有特殊的前缀A，B，C。

That is A is the highest level, and C is the lowest. Note that ids from different levels are unique, since they have a special prefix, A,B,C.

问题是当过滤多个级别时，我可能结果集为空。

The problem is when filtering in more than one level, I may have empty result set.

例如在Lvl1 = A1&上过滤Lvl2 = B3（无交集），因此将不返回任何内容。我需要获取前5行（Lvl1 = A1或Lvl2 = B3）

e.g. filtering on Lvl1=A1 & Lvl2=B3 (no intersection), and so will return nothing. What I need is to get the first 5 rows (Lvl1=A1 or Lvl2=B3)

const lvl1Filter: IBasicFilter = {
  $schema: "http://powerbi.com/product/schema#basic",
  target: {
    table: "Hierarchy",
    column: "Lvl1"
  },
  operator: "In",
  values: ['A1'],
  filterType: FilterType.BasicFilter
}

const lvl2Filter: IBasicFilter = {
  $schema: "http://powerbi.com/product/schema#basic",
  target: {
    table: "Hierarchy",
    column: "Lvl2"
  },
  operator: "In",
  values: ['B3'],
  filterType: FilterType.BasicFilter
}

report.setFilters([lvl1Filter, lvl2Filter]);

问题是过滤器彼此独立，并且它们都将被应用，即

The problem is that the filters are independent from each other, and they will both be applied, that is with AND operation between them.

那么，有没有办法在它们之间使用OR运算来发送过滤器，或者有一种模拟它的方法？

So, is there a way to send the filters with OR operation between them, or is there a way to simulate it?

PS：我试图将所有数据放在单个列中（如下表所示），并且可以正常工作，但是数据非常大（上百万条记录），因此，它非常慢，所以我需要更有效的方法。

所有数据都在单列​​中：

All data in single column:

+--------------+
| AllHierarchy |
+--------------+
| A1           |
| A2           |
| A3           |
| B1           |
| B2           |
| B3           |
| B4           |
| B5           |
| C1           |
| C2           |
| C3           |
| C4           |
| C5           |
| C6           |
| C7           |
+--------------+

设置过滤器：

const allHierarchyFilter: IBasicFilter = {
  $schema: "http://powerbi.com/product/schema#basic",
  target: {
    table: "Hierarchy",
    column: "AllHierarchy"
  },
  operator: "In",
  values: ['A1', 'B3'],
  filterType: FilterType.BasicFilter
}

report.setFilters([allHierarchyFilter]);

推荐答案

不可能直接使或成为可能在Power BI中的多个列之间进行过滤，因此您尝试将所有值合并到一个列中是正确的。但是，除了将所有可能的值合并为一列以附加所有可能的值之外，您还可以尝试按每行组合它们的值。例如，连接当前行中的所有值，或者添加一些唯一的分隔符（这取决于您的实际值，未显示）。如果所有列都将具有值，请使其简单-创建新的DAX列（而不是度量！）：

It isn't directly possible to make "or" filter between multiple columns in Power BI, so you were right to try to combine all values in a single column. But instead of appending all possible values by union them in one column, which will give you a long list, you can also try to combine their values "per row". For example, concatenate all values in the current row, maybe add some unique separator (it depends on your actual values, which are not shown). If all columns will have values, make it simple - create new DAX column (not a measure!):

All Levels = 'Table'[Lvl1] & "-" & 'Table'[Lvl2] & "-" & 'Table'[Lvl3]

如果某些级别可能为空，则可以您想要处理以下问题：

If it is possible some of the levels to be blank, you can if you want, to handle that:

All Levels = 'Table'[Lvl1] &
    IF('Table'[Lvl2] = BLANK(); ""; "-" & 'Table'[Lvl2]) &
    IF('Table'[Lvl3] = BLANK(); ""; "-" & 'Table'[Lvl3])

请注意，根据您的区域设置，您可能必须用逗号替换上面代码中的分号。

这将为您提供一个新列，其中将包含当前行的所有值，例如 A1-B2-C3 。现在，您可以创建一个过滤器所有级别包含A1或所有级别包含B3 ，现在它是单个列上的过滤器，我们可以轻松地使用或：

This will give you a new column, which will contain all values from the current row, e.g. A1-B2-C3. Now you can make a filter All Levels contains A1 or All Levels contains B3, which now is a filter on a single column and we can easily use or:

嵌入JavaScript代码时，应创建高级过滤器，如下所示：

When embedding your JavaScript code should create advanced filter, like this:

const allLevelsFilter: IAdvancedFilter = {
  $schema: "http://powerbi.com/product/schema#advanced",
  target: {
    table: "Hierarchy",
    column: "All Levels"
  },
  logicalOperator: "Or",
  conditions: [
    {
      operator: "Contains",
      value: "A1"
    },
    {
      operator: "Contains",
      value: "B3"
    }
  ],
  filterType: FilterType.AdvancedFilter
}

report.setFilters([allLevelsFilter]);

如果您需要完全匹配（例如，上面的代码还将返回带有的行A11 或 B35 ），然后在列的开头和结尾处也添加分隔符（即得到- A1-B2-C3-），并在您的JavaScript代码中在搜索字符串之前和之后附加它（即搜索 -A1-和 -B3-）。

If you need exact match (e.g. the above code will also return rows with A11 or B35), then add the separator at the start and the end of the column too (i.e. to get -A1-B2-C3-) and in your JavaScript code append it before and after your search string (i.e. search for -A1- and -B3-).

希望这会有所帮助！

这篇关于Power BI：如何在2个不同的过滤器之间使用OR？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！