英文:

How to categories the same values of a column based on other column in Power BI?

问题

I have a table named Appointments, that looks like the below table -

mrno	patencounterid	Doctor
1718	68	Dr. A
1718	69	Dr. A
1718	70	Dr. A
1718	71	Dr. B
1718	72	Dr. B
1718	73	Dr. B

I want to add a new column Old & New (Categorises the patients as Old and New).

Necessary conditions for a new patient -

1. mrno has to be the same.
2. Doctor has to be the same.
3. patencounterid has to be the smallest one.

Necessary conditions for an old patient -

1. mrno has to be the same.
2. Doctor has to be the same.
3. patencounterid should not be the smallest one.

The values in the mrno and Doctor columns are not distinct but the corresponding patencounterid values are distinct.

The result table should look like this -

mrno	patencounterid	Doctor	Old & New
1718	68	Dr. A	New
1718	69	Dr. A	Old
1718	70	Dr. A	Old
1718	71	Dr. B	New
1718	72	Dr. B	Old
1718	73	Dr. B	Old

My Take on this (which is not correct) -

Add New Column

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")```

The Above code returns the error  - A function filter has been used in a true/false statement that is used as a filter expression.

<details>
<summary>英文:</summary>

I have a table named **Appointments**, that looks like the below table - 

|mrno|patencounterid|Doctor|
|---|---|---|
|1718|68|Dr. A|
|1718|69|Dr. A|
|1718|70|Dr. A|
|1718|71|Dr. B|
|1718|72|Dr. B|
|1718|73|Dr. B|

I want to add a new column **Old &amp; New** (Categorises the patients as Old and New).

**Necessary conditions for a new patient -** 
 1. mrno has to be the same.
 2. Doctor has to be the same.
 3. patencounterid has to be the smallest one.

**Necessary conditions for an old patient -** 
 1. mrno has to be the same.
 2. Doctor has to be the same.
 3. patencounterid should not be the smallest one.

The values in the **mrno** and **Doctor** columns are not distinct but the corresponding **patencounterid** values are distinct. 

The result table should look like this - 

|mrno|patencounterid|Doctor|Old &amp; New|
|---|---|---|---|
|1718|68|Dr. A|New|
|1718|69|Dr. A|Old|
|1718|70|Dr. A|Old|
|1718|71|Dr. B|New|
|1718|72|Dr. B|Old|
|1718|73|Dr. B|Old|

My Take on this (which is not correct) -

Add New Column

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")

The Above code returns the error  - A function filter has been used in a true/false statement that is used as a filter expression.

</details>


# 答案1
**得分**: 1

以下是翻译好的代码部分：

第一个代码块：

```plaintext
Column = 

VAR maximum = 
CALCULATE(
    MAX('Appointments'[patencounterid]),
    ALLEXCEPT(Appointments, Appointments[mrno])
)

RETURN IF (Appointments[patencounterid] = maximum, "New", "Old")

第一个输出链接：[![enter image description here][1]][1]

第二个代码块：

Column = 

VAR minimum = 
CALCULATE(
    MIN('Appointments'[patencounterid]),
    ALLEXCEPT(Appointments, Appointments[mrno], Appointments[Doctor])
)

RETURN IF (Appointments[patencounterid] = minimum, "New", "Old")

第二个输出链接：[![enter image description here][2]][2]

<details>
<summary>英文:</summary>

You can try this below code for new **COLUMN**-

Column =

VAR maximum =
CALCULATE(
MAX('Appointments'[patencounterid]),
ALLEXCEPT(Appointments,Appointments[mrno])
)

RETURN IF (Appointments[patencounterid] = maximum, "New","Old")

Here is the output-

[![enter image description here][1]][1]

----------
For your new requirement, try this below code-

Column =

VAR minimum =
CALCULATE(
MIN('Appointments'[patencounterid]),
ALLEXCEPT(Appointments,Appointments[mrno],Appointments[Doctor])
)

RETURN IF (Appointments[patencounterid] = minimum, "New","Old")

Here is the output

[![enter image description here][2]][2]


  [1]: https://i.stack.imgur.com/hymB8.png
  [2]: https://i.stack.imgur.com/gvcdH.png

</details>