如何在Power BI中基于mrno和Doctor列分类标记新老患者

我有一张名为Appointments的表，数据如下：

需要添加一个名为Old & New的新列，将患者分类为新患者（New）和老患者（Old）。

判定条件 #

新患者 #

• mrno值相同
• Doctor值相同
• patencounterid为该组中的最小值

老患者 #

• mrno值相同
• Doctor值相同
• patencounterid不为该组中的最小值

注：mrno和Doctor列的值不唯一，但对应的patencounterid值是唯一的。

预期结果 #

错误实现及问题 #

尝试的DAX代码：

VAR minimum =
CALCULATE(
        MIN(Appointments[patencounterid]),
        ALLEXCEPT(Appointments,Appointments[mrno]),
        COUNTROWS(
             FILTER(Appointments, EARLIER(Appointments[mrno]) = Appointments[mrno])),
        COUNTROWS(
             FILTER(Appointments, EARLIER(Appointments[Doctor]) = Appointments[Doctor])),
        COUNTROWS(
             FILTER(Appointments, EARLIER(Appointments[patencounterid]) < Appointments[patencounterid])))
  
RETURN IF(
       Appointments[patencounterid] = minimum, "New", "Old")

返回错误提示（已翻译）：

在用作筛选表达式的真假语句中使用了函数筛选器。

解决方案 #

错误原因是CALCULATE的筛选参数需要布尔表达式或表筛选，而你用了COUNTROWS返回的数值，不符合语法要求。以下是两种正确的实现方式：

方法1：使用ALLEXCEPT分组计算最小值 #

Old & New = 
VAR MinEncounter = 
    CALCULATE(
        MIN(Appointments[patencounterid]),
        ALLEXCEPT(Appointments, Appointments[mrno], Appointments[Doctor])
    )
RETURN
    IF(Appointments[patencounterid] = MinEncounter, "New", "Old")

方法2：使用MINX结合FILTER分组 #

Old & New = 
VAR CurrentMR = Appointments[mrno]
VAR CurrentDoc = Appointments[Doctor]
VAR MinEncounter = 
    MINX(
        FILTER(Appointments, Appointments[mrno] = CurrentMR && Appointments[Doctor] = CurrentDoc),
        Appointments[patencounterid]
    )
RETURN
    IF(Appointments[patencounterid] = MinEncounter, "New", "Old")

内容的提问来源于stack exchange，提问作者Atif