DAX measure count duplicate column values on selection of columns

[Anonymous]

Hi, 

I need to count the number of duplicate 'Artikel' for every 'DC' and must use a DAX measure for this.

I have this table: 

DC	RC Category	RC	Artikel	Date
A	ID	damaged	appel	2/01/2023 0:00
A	ID	damaged	peer	2/01/2023 0:00
A	ID	expired	appel	2/01/2023 0:00
A	ID	damaged	appel	1/01/2023 0:00
A	ID	damaged	peer	1/01/2023 0:00
A	ID	expired	appel	1/01/2023 0:00
B	ID	damaged	appel	1/01/2023 0:00
B	UNID	damaged	banaan	1/01/2023 0:00
B	ID	expired	banaan	1/01/2023 0:00
B	ID	expired	peer	1/01/2023 0:00

I want to come to this result:

DC	RC Category	RC	Artikel	Measure
A	ID	damaged	appel	2
A	ID	damaged	peer	1
A	ID	expired	appel	2
B	ID	damaged	appel	1
B	UNID	damaged	banaan	2
B	ID	expired	banaan	2
B	ID	expired	peer	1

I tried to use SUMMARIZE, ALLEXCEPT, CALCULATE, COUNT but can't figure it out....

Thanks for the help

Julie

[v-yangliu-msft]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _count1=
COUNTX(
    FILTER(ALL('Table'),
    'Table'[DC]=MAX('Table'[DC])&&'Table'[Artikel]=MAX('Table'[Artikel])),[Artikel])
var _count2=
CALCULATE(
    DISTINCTCOUNT('Table'[Date]),
    FILTER(ALL('Table'),
    'Table'[DC]=MAX('Table'[DC])&&'Table'[RC]=MAX('Table'[RC])))
return
DIVIDE(
    _count1,_count2)

2. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@Anonymous , I am getting this with simple count. How you are getting value 2 for UNID ?

[Anonymous]

Hi @amitchandak ,

For value 'B' in column DC there are 2 'banaan' in column Artikel. I need to count the number of times one Artikel comes back in a DC. Regardless of the other selected columns.
Also the Date column is part of my datatable but in the table visual I dont included it so I dont wont it to affect the count (example Artikel 'Appel' in DC 'A' should return 2 in table visual and not 4).