Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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
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).
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |