Forum Discussion
New Table with max Defects of each File
Hello everyone,
I am new to Power BI and DAX. I searched the forum for my problem but cant make it work.
I get my Data in the following Format. For each Coil there is a unique File that numbers the Defects in the order they occur and puts them in their Defect Category. There are about 20 different categories. In the example below i simplified it a bit.
| File Name | Order Number | Coil Number | Defect Category | Defect Number |
| 001-R1.csv | 001 | R1 | A | 1 |
| 001-R1.csv | 001 | R1 | A | 2 |
| 001-R1.csv | 001 | R1 | B | 3 |
| 001-R1.csv | 001 | R1 | A | 4 |
| 001-R2.csv | 001 | R2 | B | 1 |
| 001-R2.csv | 001 | R2 | A | 2 |
| 001-R2.csv | 001 | R2 | A | 3 |
| 001-R2.csv | 001 | R2 | B | 4 |
| 001-R2.csv | 001 | R2 | B | 5 |
| 002-R1.csv | 002 | R1 | A | 1 |
| 002-R1.csv | 002 | R1 | B | 2 |
| 002-R1.csv | 002 | R1 | A | 3 |
| 002-R2.csv | 002 | R2 | A | 1 |
| 002-R2.csv | 002 | R2 | A | 2 |
| 002-R2.csv | 002 | R2 | A | 3 |
| 002-R2.csv | 002 | R2 | A | 4 |
| 002-R2.csv | 002 | R2 | B | 5 |
Now i want to create a new table that lists the unique file name and the according number of Defects for each Category.
| File Name (ID) | Defect Count Class "A" | Defect Count Class "B" |
| 001-R1 | 3 | 1 |
| 001-R2 | 2 | 3 |
| 002-R1 | 2 | 1 |
| 002-R2 | 4 | 1 |
I already listed all the File Names with the DISTINCT Function
File Name (ID) = DISTINCT(Table[File Name])But i dont know how to count the number of defects for each class for the corresponding file name.
Hi Philip_K6
Thanks for your reply.
>> i want to create a new table that lists the unique file name and the according number of Defects for each Category.... I wanted to create a seperate table not a measure. ...I already listed all the File Names with the DISTINCT Function
Oh I got it. So we only need to perform distinct count on the basis of the file name list you created.
Please try this, create the 2 columns in your list table,
Defect Count A = COUNTX(FILTER('Table','Table'[File Name]=EARLIER('File Name (ID)'[File Name]) && 'Table'[Defect Category]="A" ),[Coil Number])Defect Count B = COUNTX(FILTER('Table','Table'[File Name]=EARLIER('File Name (ID)'[File Name]) && 'Table'[Defect Category]="B" ),[Coil Number])result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- amitchandak
Super User
Philip_K6 , You should able to do that using a measure
count(Table[Defect Number])
And plot in visual with file name and class
amitchandak thank you for your reply.
What i eventually want to do is create a histogram that shows the number of coils that lie in a specific range of defects filtered by the Defect Class. Thats why I wanted to create a seperate table like I said in my post and not a measure. But if you think thats possible with a measure please tell me how.
v-xiaotangCommunity Support
Hi Philip_K6
Thanks for reaching out to us.
>> expected result
You can try this,
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
v-xiaotang thank you for your reply.
Thats what I want to do but not as a visual. I want to reference that table again so I can group the seperate coils into ranges of defects. Something like this:- v-xiaotang
Hi Philip_K6
Thanks for your reply.
>> i want to create a new table that lists the unique file name and the according number of Defects for each Category.... I wanted to create a seperate table not a measure. ...I already listed all the File Names with the DISTINCT Function
Oh I got it. So we only need to perform distinct count on the basis of the file name list you created.
Please try this, create the 2 columns in your list table,
Defect Count A = COUNTX(FILTER('Table','Table'[File Name]=EARLIER('File Name (ID)'[File Name]) && 'Table'[Defect Category]="A" ),[Coil Number])Defect Count B = COUNTX(FILTER('Table','Table'[File Name]=EARLIER('File Name (ID)'[File Name]) && 'Table'[Defect Category]="B" ),[Coil Number])result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Thank you v-xiaotang thats what i wanted to do!
