DistinctCount for matching values on two columns

allejot , Create a new column in Table 2

new column =countx(filter(table1,table1[ID] =table2[ID]),table[ID])

any value means that is present in table 1

Hi, allejot 

Based on your desscription, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

There is no relationship between two tables. You may create calculated columns or measures as below.

Calculated column:

Count Column = 
COUNTROWS(
    FILTER(
        Table2,
        Table2[ID]=EARLIER(Table1[ID])
    )
)

Sum Column = 
CALCULATE(
    SUM(Table2[Quantity]),
    FILTER(
        Table2,
        Table2[ID]=EARLIER(Table1[ID])
    )
)

Measure:

Count Measure = 
COUNTROWS(
    FILTER(
        Table2,
        Table2[ID]=SELECTEDVALUE(Table1[ID])
    )
)

Sum Measure = 
CALCULATE(
    SUM(Table2[Quantity]),
    FILTER(
        Table2,
        Table2[ID]=SELECTEDVALUE(Table1[ID])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Guys I tried all proposed solution but none worked for me.

I tried to literally copy yours approach but none worked. I think the issue is that I have a many to many relationship (with a table in between). I perhaps didn't give a detail that makes the formula more difficult.

Table 1: actual hours worked on a given taskID in a given day

Table 2: planned hours on a given taskID in a given day

Table 3: list of all TaskID with TaskName

Table 1: RegistrationDate, ID, Quantity

Table 2: PlanningDate, ID, Quantity

Table 3: ID, IDName

In fact, I need to:

- count how many IDs we actually worked that were planned

- sum the hours actually worked in the IDs that were planned.