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   |
+------+------+------+

其中有一些类似于层次结构的东西.

In which it is some thing like a hierarchy.

当用户选择A1时，他实际上选择了前3行，B1选择了前2行，C1只选择了第一行.

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]);

问题是过滤器是相互独立的，它们都会被应用，也就是它们之间的AND操作.

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:我尝试将所有数据放在单个列中(如下表)，并且成功了，但是数据非常大(数百万条记录)，所以非常非常慢，所以我需要更高效的东西.

单列中的所有数据:

+--------------+
| 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.现在你可以制作一个过滤器All Levels contains A1 or All Levels contains 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屋！