topic Re: Count of Duplicates based on other Column Criteria in DAX Commands and Tips

Count of Duplicates based on other Column Criteria

Mikey575 — Mon, 23 Oct 2023 14:11:08 GMT

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.

Re: Count of Duplicates based on other Column Criteria

wdx223_Daniel — Tue, 24 Oct 2023 00:04:19 GMT

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

Re: Count of Duplicates based on other Column Criteria

Mikey575 — Tue, 24 Oct 2023 11:23:06 GMT

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.

Re: Count of Duplicates based on other Column Criteria

FreemanZ — Tue, 24 Oct 2023 14:43:59 GMT

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:

Re: Count of Duplicates based on other Column Criteria

EylesIT — Tue, 24 Oct 2023 16:38:50 GMT

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.

Re: Count of Duplicates based on other Column Criteria

Mikey575 — Tue, 24 Oct 2023 16:44:09 GMT

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.

Re: Count of Duplicates based on other Column Criteria

wdx223_Daniel — Tue, 24 Oct 2023 23:42:21 GMT

if SIPP appeared firstly it will give a wrong result?

Re: Count of Duplicates based on other Column Criteria

wdx223_Daniel — Tue, 24 Oct 2023 23:43:35 GMT

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

Re: Count of Duplicates based on other Column Criteria

FreemanZ — Wed, 25 Oct 2023 01:23:50 GMT

yes, indeed.

Re: Count of Duplicates based on other Column Criteria

FreemanZ — Wed, 25 Oct 2023 01:39:45 GMT

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.