Get row count (With condition?) from another table

[Anonymous]

Hi all,

First time posting here, I'm also new to Power BI and DAX.

I would like to get the row count from another table. There are 2 tables, Yield Table and Defect Table

Yield Table (Summarised):

Date	Model	Process	Total Qty
20/2/2022	ABC	A	10
21/2/2022	ABC	B	14
21/2/2022	DEF	A	15

Defect Table (Summarised):

Date	Model	Process	Defect Type
21/2/2022	ABC	A	D1
21/2/2022	ABC	A	D1
22/2/2022	DEF	A	D2

I've made 2 Matrix visuals, Yield Matrix and Defect Matrix:

Yield Matrix (I would like to add the Defect D1, row count of defect type 'D1' from the defect table)

Date	Model	Process	Total Qty	Defect D1
February 20	ABC	A	10	2
21	ABC	A	0	0
		B	14	0

Defect Matrix 

Date	Model	Process	Defect D1
February 20	ABC	A	2
21	GHI	C	2
		D	1

I was able to add a new measure to get the Defect D1 (Row count of defect type 'D1') using this formula:

Defect D1= CALCULATE(COUNTX(FILTER(Defects, Defects[Defect Type] = "D1"), Defects[Defect Type]))

I connected the 2 tables using Model (Many to Many), when I try to directly add the measure I added into the Yield Matrix, it will return the row count of defect type 'D1' from the whole table. For example:

Date	Model	Process	Total Qty	Defect D1
February 20	ABC	A	10	2
21	ABC	A	0	2
		B	14	2

This is wrong because the row count should be based on the date, model and process. I have thought of making a new table to store the row count for every different date, model and process, but I'm not sure on how to do so, if this new table can be made, will it be updated automatically when the defect table is updated? 

Can anyone help me to solve this issue? Thanks in advance.

[VahidDM]

HI @Anonymous 

Can you please add moe details about Defect Matrix. how did you create that and the logic behind that?

Also can you please add the desired output that you are looking for?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hi @VahidDM ,

The defect matrix is just to get the row count of Defect 'D1', for each day, model and process.

Defect Matrix Fields :

Rows :         

- Date

- Model

- Process

Value :

- Defect D1

Date	Model	Process	Defect Type
20/2/2022	ABC	A	D1
20/2/2022	ABC	A	D1
21/2/2022	DEF	A	D1
21/2/2022	DEF	B	D1
21/2/2022	ABC	A	D1
21/2/2022	ABC	A	D2

If I was to create a new table to store the data (Defect table) above, it should look like this :

Date	Model	Process	Defect D1
20/2/2022	ABC	A	2
21/2/2022	DEF	A	1
21/2/2022	DEF	B	1
21/2/2022	ABC	A	1

My desired output is to add the Defect D1 measure in the Yield Matrix, according to the Date, Model and Process of both table as shown on the Topic (Yield Matrix). Then, I would use this measure to deduct the Total field in the Yield Matrix.

I think that maybe I need to add more relationship between the 2 tables and the creation of new table is unnecessary, but I'm not sure how to do it.

[amitchandak · Accepted solution]

@Anonymous , ideal is you create Date, Model, Process table and join with both table and analyze the data with help of those. You need to be in star schema

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

For date you can create a date table using calendar

for model and Process , you can create table like

Model  = distinct(union(distinct(Table1[Model]),  distinct(Table2[Model]) ))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi @amitchandak,

I'm not sure if I did it correctly, I just added 3 Dim tables each with 1 column and connected them to both of the table with one to many. Then, I updated my matrix fields with the new table columns, I think it is working.

Thanks a lot for your help! Much appriciated!! Please let me know if there were any mistakes I made.