搜索具有相同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".以下内容的基本原理是，我可以算出相应ID从 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

推荐答案

这就是您要尝试做的事情.

This would be what you are trying to do.

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屋！