New Table with max Defects of each File

[Philip_K6]

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.

[Philip_K6]

@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 · Accepted solution]

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.

[Philip_K6]

Thank you @v-xiaotang thats what i wanted to do! 

[v-xiaotang]

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.

[Philip_K6]

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

[amitchandak]

@Philip_K6 , You should able to do that using a measure

count(Table[Defect Number])

And plot in visual with file name and class