Counting rows, that have a duplicate value in one column and either value 2 or 3 in another column

[Fromit87]

Hi!

I have a dataset that has duplicates in column A. When there are duplicates in column A, I like to filter column B by the following condition: if count of text value in column A > 1 (check if duplicate) and Column B value for all duplicated entries is either 1 or 3 specifically, then count each row towards a total.

The data looks as follows:

Column A	Column B	Column C (count)
Car1	2	0
Car2	2	0
Car2	2	0
Car3	2	0
Car3	1	0
Car4	3	1
Car4	1	1
Car4	2	0

The expected result here should count only rows with value Car4 in A and values 3 and 1 in B, because there is more than one entry for Car4 and the condition in column B is only met for two out of the three Car4 rows.

I already have a calculated column that tells me if column A has duplicate values. But I don't know how to bring it together with the criteria filter from column B.

Any help is highly appreciated! Thank you!

[VahidDM · Accepted solution]

Hi @Fromit87 

Try this code to add a new column to your table:

Column =
VAR _A =
    FILTER ( 'Table', 'Table'[Column A] = EARLIER ( 'Table'[Column A] ) )
VAR _CA =
    COUNTAX ( _A, [Column A] )
VAR _1 =
    COUNTX (
        FILTER (
            'Table',
            'Table'[Column A] = EARLIER ( 'Table'[Column A] )
                && 'Table'[Column B] = 1
        ),
        [Column B]
    )
VAR _3 =
    COUNTX (
        FILTER (
            'Table',
            'Table'[Column A] = EARLIER ( 'Table'[Column A] )
                && 'Table'[Column B] = 3
        ),
        [Column B]
    )
RETURN
    IF (
        'Table'[Column B] = 1
            || 'Table'[Column B] = 3,
        IF ( _CA > 1 && _1 <> 0 && _3 <> 0, 1, 0 ),
        0
    )

Output:

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

Appreciate your Kudos!!

[Fromit87]

Thank you @VahidDM  that works perfectly. It took me a little time to understand what the code is doing, but it makes complete sense to me now.

One bonus question, if I may: if column B would be located in a different table than column A (but both tables are in a 1:n relationship), how would that code change? With countx and filter I am only able to operate within one table, or is it possible to use the existing relationship to another table? Thanks!