Count distinct ID if group by ID_2 greater than 1

[mellomoon3]

I have the following table:

ID     ID_2     Date

1      234       Jan '24

1      829       Feb '24

2      981       Jan '24

2      146       Mar '24

2      286       Nov '24

3      321       May '24

3      743       Feb '24

I want to create a measure in Power BI so that I am able to count distinct ID if there are more than 1 ID_2. For example, from Jan '24 to Dec '24, there are 3 IDs that have more than 1 ID_2 and from Jan '24 to Mar '24, there are 2 IDs that have more than 1 ID_2.

I tried creating two measures and it gave me the desired output, but it does not take into account for the Date filter which I want it to be able to.

Measure 1 = CALCULATE(DISTINCTCOUNT(ID_2), ALLEXCEPT(ID))

Measure 2 = CALCULATE(DISTINCTCOUNT(ID), FILTER([Measure 1] > 1))

Appreciate any guidance. Thanks in advance!

[miTutorials · Accepted solution]

Try the below measure and make sure your the data type of your Date column is a Date and not Text.

DistinctID_Count = 
VAR IDsWithMultipleID2 =
    ADDCOLUMNS(
        VALUES('Table'[ID]),
        "@ID2Count", 
            CALCULATE(DISTINCTCOUNT('Table'[ID_2]))
    )

RETURN
CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(IDsWithMultipleID2, [@ID2Count] > 1)
)

Sample pbix attached..

[mellomoon3]

This works, thank you for your help!