powerbi - Power BI DAX：使用行对筛选器上下文计数不同的度量

Question

现在坐久一点，这是我的数据：

+------------+---------+------------+
| OrderID    | Status  | Text       |
+------------+---------+------------+
| 1          | rel     | W3-A       |
+------------+---------+------------+
| 1          | conf    | log        |
+------------+---------+------------+
| 3          | rel     | W3-A       |
+------------+---------+------------+
| 4          | rel     | W3-B       |
+------------+---------+------------+
| 5          | rel     | W3-C       |
+------------+---------+------------+
| 6          | rel     | W3-B       |
+------------+---------+------------+
| 6          | conf    | log        |
+------------+---------+------------+
| 7          | conf    | log        |
+------------+---------+------------+
| 8          | rel     | W3-B       |
+------------+---------+------------+
| 8          | rel     | log        |

现在我想要一个显示：

=计算每个具有 (Status=rel && text= 以 "W3" 开头) 但也有一行 (Status=conf && text=log) 的不同 orderID

这将导致总数为“2”：OrderID 1 和 OrderID6 满足这些条件

作为表格，它看起来像这样：

+------------+---------+------------+------------+
| OrderID    | Status  | Text       | distinctCount
+------------+---------+------------+------------+
| 1          | rel     | W3-A       |   1
+------------+---------+------------+------------+
| 1          | conf    | log        |   1
+------------+---------+------------+------------+
| 3          | rel     | W3-A       |
+------------+---------+------------+------------+
| 4          | rel     | W3-B       |
+------------+---------+------------+------------+
| 5          | rel     | W3-C       |
+------------+---------+------------+------------+
| 6          | rel     | W3-B       |   1
+------------+---------+------------+------------+
| 6          | conf    | log        |   1
+------------+---------+------------+------------+
| 7          | conf    | log        |
+------------+---------+------------+------------+
| 8          | rel     | W3-B       |
+------------+---------+------------+------------+
| 8          | rel     | log        |
-------------+---------+------------+------------+
TOTAL                                    2

因此，在“卡片”可视化中拖动度量应该简单地显示

 Order CountDist
    +--------------+
           2
    +--------------+

标签： powerbidaxpowerbi-desktop

Answer 1

您可以找到满足每组条件的 ID，然后通过交集找出满足这两个条件的 ID。

DistinctCountPairs =
VAR rel_W3 =
    CALCULATETABLE (
        DISTINCT ( Orders[OrderID] ),
        Orders[Status] = "rel",
        LEFT ( Orders[Text], 2 ) = "W3"
    )
VAR conf_log =
    CALCULATETABLE (
        DISTINCT ( Orders[OrderID] ),
        Orders[Status] = "conf",
        Orders[Text] = "log"
    )
RETURN
    COUNTROWS ( INTERSECT ( rel_W3, conf_log ) )

---

如果需要，可以将最后一行切换为

CONCATENATEX ( INTERSECT ( rel_W3, conf_log ), Orders[OrderID], "," )

打印交叉点中的 ID 列表。