Forum Discussion
Get row count (With condition?) from another table
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
|
|
|
|
|
|
|
|
|
|
|
|
|
Defect Matrix
| Date | Model | Process | Defect D1 |
February
|
|
|
|
|
|
|
|
|
|
I was able to add a new measure to get the Defect D1 (Row count of defect type 'D1') using this formula:
| Date | Model | Process | Total Qty | Defect D1 |
February
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
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.
4 Replies
- VahidDM
Super User
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/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.
