cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## DAX measure count duplicate column values on selection of columns

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

3 REPLIES 3
Community Support

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

Super User

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

Anonymous
Not applicable

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors