topic Re: DAX measure count duplicate column values on selection of columns in DAX Commands and Tips

DAX measure count duplicate column values on selection of columns

Anonymous — Wed, 18 Jan 2023 16:02:07 GMT

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

Re: DAX measure count duplicate column values on selection of columns

amitchandak — Wed, 18 Jan 2023 17:19:57 GMT

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

Re: DAX measure count duplicate column values on selection of columns

Anonymous — Thu, 19 Jan 2023 05:55:30 GMT

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

Re: DAX measure count duplicate column values on selection of columns

Anonymous — Thu, 19 Jan 2023 09:42:29 GMT

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).