Forum Discussion
Get row count (With condition?) from another table
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.
Super UserHI 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/
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
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.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.