DistinctCount for matching values on two columns

[allejot]

Hi,

I am strugling with the DAX formula (new to DAX..) for counting how many rows in one table match the values in another one. Here the example:

Table 1 = ID, quantity

Table 2 = ID, quantity

I need to sum the quantity of ID in Table 2 being present in Table 1. Any suggestion on this?

Thanks, Alex

[allejot]

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.

[v-alq-msft]

Hi, @allejot 

We don't recommend you relate many-to-many dimension-type tables directly. Please refer to the following guidance:

• Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
• Add a bridging table to store associated entities
• Create one-to-many relationships between the three tables
• Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
• When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
• Hide the bridging table (unless it contains additional columns or measures required for reporting)

For further information, you may refer to the document .

Best Regards

Allan

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

[v-alq-msft]

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.

[amitchandak]

@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

[Anonymous]

If I understand your question correctly, you can use this measure to calculate the amount of matches between table 1 & table 2:

countMatches = CALCULATE(COUNT(tbl1[ID]) , FILTER(tbl1,LOOKUPVALUE(tbl2[ID],tbl2[ID], tbl1[ID])))