Compare 2 rows in same table of PowerBI

Question

I am trying to create a column to see if an ID equals another ID with the same month. It should show "NO" but if an ID equals another ID with a different month. It should show "YES". Other show "NO". see as below

ID        Month            Duplicate
1          4                 No
1          4                 No
2          5                 No
2          6                 Yes
2          7                 Yes
3          8                 No
4          6                 No
4          6                 No
4          7                 Yes
4          8                 Yes
5          6                 No
5          6                 No
5          6                 No

My code like this

Duplicate =
IF (
    COUNTROWS ( FILTER ( Data, Data[Policy No] = EARLIER( Data[Policy No]) ) )
        > 1
    && COUNTROWS ( FILTER ( Data, Data[Month] < EARLIER(Data[Month]) ) ),
    "YES",
    "NO")

but it is not correct because when I select the first month as month 4 it shows no but when I select month 5 it shows yes. Like as below

ID        month             Duplicate
1          4                 No
1          4                 No
2          5                 Yes
2          5                 Yes

Please help me resolve this Thank you

OscarLar OscarLar · Accepted Answer · 2021-09-02T08:47:03

Not sure I understand the logic you want to implement but i THINK this will do what you're looking for (providing I did understand...).

I prefer to use variables rather than the EARLIER function. IMHO it makes the code easier to understand and more readable, especially for others.

This is a calculated column in your 'Data' table

Check = 
var _id = [ID]
var _month = [Month]

var _check_both = 
CALCULATE(
    COUNTROWS('Data'),
    FILTER(
        ALL('Data'),
        'Data'[ID] = _id && 'Data'[Month] = _month
    )
)

var _check_id = 
CALCULATE(
    COUNTROWS('Data'),
    FILTER(
        ALL('Data'),
        'Data'[ID] = _id 
    )
)
return
SWITCH(
    TRUE(),
    _check_both > 1, "No",
    _check_id > 1, "Yes",
    "NO"
)

Compare 2 rows in same table of PowerBI

1 Answers