Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
@Anonymous , I am getting this with simple count. How you are getting value 2 for UNID ?
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).
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |