搜索具有相同 ID 的行并应用 AND 过滤器来计算特定出现次数 [英] Search rows with the same ID and apply AND filter to count a particular occurrence

问题描述

我是 PowerBi 和 DAX 的新手，现在遇到了一个我似乎无法解决的小问题.

I'm fairly new with PowerBi and DAX, and have now run into a small issue that I can't seem to wrap my head around.

假设我有下表:

  ID  Type
+---+-------+
| 1 | type1 |
+---+-------+
| 1 | type2 |
+---+-------+
| 1 | type1 |
+---+-------+
| 1 | type4 |
+---+-------+
| 2 | type1 |
+---+-------+
| 2 | type1 |
+---+-------+
| 2 | type1 |
+---+-------+

在我的任务中，我希望能够计算有多少我的 ID 将类型从 type1 更改为 type2.在以下情况下，ID = 1 是唯一改变其类型的实体.

In my task, I want to be able to count how many of my IDs that have changed type from type1 to type2. In the following case, ID = 1 is the only entity which change its type.

我最初的想法是使用相应的 DAX 代码进行测量:

My initial idea was to do a measure with the correpsonding DAX code:

Measure = Calculate(
    DISTINCTCOUNT('Table'[ID]), 
    FILTER('Table' AND 'Table'[Type] = "type1" && 'Table'[Type] = "type2"))

然而，上面确实返回null".下面的基本原理是认为我可以计算相应类型从 type1 更改为 type2

The above does however return "null". The rationale behind the following was the thought that I could count the distinct IDs where the corresponding type had changed from type1 to type2

推荐答案

这就是你想要做的.

Count IDs With Type1 And Type2
= COUNTROWS (
    FILTER (
        /* Distinct values of IDs */
        VALUES ( Table1[ID] ),
        /* Keep only IDs which contains both type1 and type2 */
        VAR _Types = CALCULATETABLE ( VALUES ( Table1[Type] ) )
        RETURN
            CONTAINS ( _Types, Table1[Type], "type1" )
            && CONTAINS ( _Types, Table1[Type], "type2" )
    )
)

这篇关于搜索具有相同 ID 的行并应用 AND 过滤器来计算特定出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！