Count of Duplicates based on other Column Criteria

[Mikey575]

In the table below if a policy number is duplicated then it means that client has more than one product.

For Example ED99887 has a 'Cash' Product and a 'SIPP' Product. I need a solution that can count that example as 1 but also exclude any other examples where the Products are different.

For Example ED47891 is duplicated and has a 'Pension' Product and a 'SIPP' Product but doesn’t need to be counted because the Products are different to 'Cash' & 'SIPP'.

It also needs to exclude non duplicated policy numbers and where there are more than 2 policy numbers duplicated (e.g. ED14522).

ED1455 is duplicated and has a products 'Cash' & 'SIPP' but also needs to be excluded because it also has the product 'Pension' linked to the policy number.

In the table below once the calulation is done I need the answer to be 3 because there are only 3 occurances where a client has a Cash Product & a SIPP Product.

[wdx223_Daniel · Accepted solution]

=COUNTROWS(FILTER(ALL(Table[Policy Number]),VAR _p=CALCULATETABLE(VALUES(Table[Product])) RETURN COUNTROWS(_p)=2&&ISEMPTY(EXCEPT(_p,{"Cash","SIPP"}))))

[EylesIT]

@Mikey575, here is my suggested solution.

Create a measure as per below

Has only 1 SIPP and only 1 CASH = 
    SUMX(
        VALUES(YourTable[Policy Number]),
        VAR policynumber = YourTable[Policy Number]

        VAR cashproducts = CALCULATE(
            COUNTROWS(YourTable),
            YourTable[Product] = "Cash", YourTable[Policy Number] = policynumber
        )

        VAR sippproducts = CALCULATE(
            COUNTROWS(YourTable),
            YourTable[Product] = "SIPP", YourTable[Policy Number] = policynumber
        )

        VAR totalproducts = CALCULATE(COUNTROWS(YourTable), YourTable[Policy Number] = policynumber)
        VAR result = IF(cashproducts = 1 && sippproducts = 1 && totalproducts = 2, 1)
        RETURN result
    )

Add a table visual to the report and drag in Policy Number and this measure. This gives the output below:

Hope this helps.

[Mikey575]

@wdx223_Daniel Thanks for your solution but based on my table it is giving me the answer of 5 when the answer I need is 3.

The senario is if a policy number is duplicated then that means there is more than 1 product. If there are 2 policy numbers the same then there are 2 products. 3 same policy numbers = 3 Products and so on.

I need to know how many clients have 2 products and the 2 products they have are SIPP & Cash. Any other senario doesnt need to be counted.

Therefore what I need the calculation to do is count the 3 cases where the Policy Number appears only twice and each 1 is Product SIPP & Product Cash. In my table example the 3 Policy numbers it should only be counting are ED23567, ED58967 & ED99887.

Many thanks for looking at this.

[wdx223_Daniel · Accepted solution]

=COUNTROWS(FILTER(ALL(Table[Policy Number]),VAR _p=CALCULATETABLE(VALUES(Table[Product])) RETURN COUNTROWS(_p)=2&&ISEMPTY(EXCEPT(_p,{"Cash","SIPP"}))))

[FreemanZ]

hi @Mikey575 ,

Amazed by the brevity of @wdx223_Daniel 's code and found it would miscount ED34521 and ED74562. 

Try like:

Measure = 
COUNTROWS(
    FILTER(
        ALL(data[Policy Number]),
        CONCATENATEX(
            CALCULATETABLE(VALUES(data[Product])),
            data[Product],
            ", "
        ) = "Cash, SIPP"
    )
)

it worked like:

[wdx223_Daniel]

if SIPP appeared firstly it will give a wrong result?

[FreemanZ]

yes, indeed.

[Mikey575]

@FreemanZ Fantastic, that has worked for me. Is there an easy way to convert the formula so it counts those with with 3 Products, Cash, SIPP & ISA. All the other principles apply that is If you have 3 Products then the Policy Number will be the same for all 3. 

[FreemanZ · Accepted solution]

hi @Mikey575 

are you expecting like:

Measure 2 = 
COUNTROWS (
    FILTER (
        ALL ( data[Policy Number] ),
        VAR _p =
            CALCULATETABLE ( VALUES ( data[Product] ) )
        RETURN
            COUNTROWS ( _p ) = 3
                && ISEMPTY ( EXCEPT ( _p, { "Cash", "SIPP", "ISA" } ) )
    )
)

Again, all credit to Daniel.

[wdx223_Daniel]

=COUNTROWS(FILTER(ALL(Table[Policy Number]),ISEMPTY(EXCEPT(CALCULATETABLE(VALUES(Table[Product])),{"Cash","SIPP"}))))