How to use (COUNTIF) function in Power BI Desktop between 2 tables

[MhdMur]

Hi,

I have 2 data tables and each of them have in common a column with a unique code for each row, "customer ID"

I linked the 2 tables using the common unique value "customer ID"

My objective is to count which and how many of each unique code "customer ID" in [Table 1] are existing in [Table 2] , and to return zero count if the unique value doesn't exist at all in [Table 2]

I've read and watched alot of explinations but non of them to work out for me

[Anonymous · Accepted solution]

Hi @MhdMur ,
Based on your description, and based on my understanding, if there are duplicate values in Table II, I'm not quite sure if you need to count all Table I values that appear in Table II, or just how many unique Table I values appear in Table II i.e. de-duplicated. You can try the following two methods.
Here are my test data:

1. Calculate contains duplicate values

Count of Customer ID in Table 1 = 
COUNTROWS(
    FILTER(
        'Table1',
        NOT(ISBLANK('Table1'[Table1 customer ID]))
            &&
        CALCULATE(
            COUNTROWS('Table2'),
            FILTER(
                'Table2',
                'Table2'[Table2 customer ID] = 'Table2'[Table2 customer ID]
            )
        ) > 0
    )
)

2. Calculate without duplicates

Count = COUNTROWS(
    FILTER(
        'Table1',
        NOT(ISBLANK(
            LOOKUPVALUE(
                'Table2'[Table2 customer ID],
                'Table2'[Table2 customer ID],
                'Table1'[Table1 customer ID]
            )
        )
    )
)
)

Best regards

Albert He

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

 

[MhdMur]

It either turns bakc with blank results or all 0 as shown below , whten trying another sequesnes it gives below error

forgive me I'm still a beginner 

[zenisekd]

Hi, create a table visual, 
first columumn is Customer ID from table 1 
second dolumn is a measure = count(customer id from table 2) +0

There you go.

Kudos and mark as solution appreciated.