Forum Discussion

MhdMur's avatar
MhdMur
New Member
2 years ago
Solved

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

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

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

     

     

  • zenisekd's avatar
    zenisekd
    Icon for Super User rankSuper User
    2 years ago

    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.

  • MhdMur's avatar
    MhdMur
    New Member
    2 years ago

    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