Forum Discussion

allejot's avatar
allejot
Frequent Visitor
6 years ago

DistinctCount for matching values on two columns

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, quantit...
Need Help
v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

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.