Forum Discussion
Creating a Table from Filtering two tables Data and Mapping
Your tables have a many to many relationship which is not ideal. But for the purpose of your question it will work.
For your "Status" column you need to do some more work as Power BI has no idea what Normal/Warning/Critical means and which one is more important. You can add a reference table for that or add a numeric value into Table 2.
See attached.
Hi lbendlin ,
Case 1:
Table1 |
|
ID | Account ID |
1811 | 3630 |
Table2
Account Number | BilliD | Product Name | Version | Acc Status |
3630 | NA | P1 | 1 | Warning |
3630 | NA | P2 | 2 | Normal |
3630 | NA | P3 | 2 | Normal |
3630 | NA | P4 | 10 | Normal |
So it should display All the Products as Billing GSID value is not matching with Table 1 of Account Number.
Acc Status may have Critical, Warning, Normal as status in above for 3630 we have Warning and Normal Status now we have to display Warning as status which is high value this value should be displaying Warning in New Status Column.
Output
ID | Name | Status | Account ID | Acc Status |
1811 | P1 | Warning | 3630 | Warning |
1811 | P2 | Warning | 3630 | Normal |
1811 | P3 | Warning | 3630 | Normal |
1811 | P4 | Warning | 3630 | Normal |
Case2 :
Table1 |
|
ID | Account ID |
3291 | 7625 |
Table2
Account Number | BilliD | Product Name | Version | Acc Status |
7625 | 3291 | P5 | 2 | Warning |
7625 | 3291 | P6 | 9 | Warning |
7625 | 4691 | P8 | 2 | Warning |
7625 |
| P9 | 6 | Normal |
7625 |
| P10 | 7 | Critical |
7625 |
| P11 | 8 | Normal |
in the Above scenario ID value is 3291 is matching with BilliD 3291 which has two records now output will be
Output:
ID | Name | Status | Account ID | Acc Status |
3291 | P5 | Warning | 7625 | Warning |
3291 | P6 | Warning | 7625 | Warning |
Case 3:
Table1 |
|
ID | Account ID |
5567 | 7625 |
table2
Account Number | BilliD | Product Name | Version | Acc Status |
7625 | 3291 | P5 | 2 | Warning |
7625 | 3291 | P6 | 9 | Warning |
7625 | 4691 | P8 | 2 | Warning |
7625 |
| P9 | 6 | Normal |
7625 |
| P10 | 7 | Critical |
7625 |
| P11 | 8 | Normal |
In the above case there is no BilliD is matching with your account hence needs to display all the products.
Output:
ID | Name | Status | Account ID | Acc Status |
5567 | P5 | Critical | 7625 | Warning |
5567 | P6 | Critical | 7625 | Warning |
5567 | P8 | Critical | 7625 | Warning |
5567 | P9 | Critical | 7625 | Normal |
5567 | P10 | Critical | 7625 | Critical |
5567 | P11 | Critical | 7625 | Normal |
Case 4:
Table1 |
|
Account ID | ID |
3631 | 5567 |
Account Number | BilliD | Product Name | Version | Acc Status |
3631 | 5567 | P12 | 7 | Normal |
3631 | 5567 | P13 | 10 | Normal |
3631 |
| P14 | 5 | Critical |
In the above case ID 5567 Is matching with BilliD 5567 hence that values should display
Output:
ID | Name | Status | Account ID | Acc Status |
5567 | p12 | Normal | 3631 | Normal |
5567 | p13 | Normal | 3631 | Normal |
Case 5:
Table1 |
|
ID | Account ID |
3201 | 3631 |
3631 | 5567 | P12 | 7 | Normal |
3631 | 5567 | P13 | 10 | Normal |
3631 |
| P14 | 5 | Critical |
In the above 3201 is not matching with any bill id hence all the products should display
Output:
ID | Name | Status | Account ID | Acc Status |
3201 | p12 | Critical | 3631 | Normal |
3201 | p13 | Critical | 3631 | Normal |
3201 | p14 | Critical | 3631 | Critical |
Overall output with all above cases is
ID | Name | Status | Account ID | Acc Status |
1811 | P1 | Warning | 3630 | Warning |
1811 | P2 | Warning | 3630 | Normal |
1811 | P3 | Warning | 3630 | Normal |
1811 | P4 | Warning | 3630 | Normal |
3291 | P5 | Warning | 7625 | Warning |
3291 | P6 | Warning | 7625 | Warning |
5567 | P5 | Critical | 7625 | Warning |
5567 | P6 | Critical | 7625 | Warning |
5567 | P8 | Critical | 7625 | Warning |
5567 | P9 | Critical | 7625 | Normal |
5567 | P10 | Critical | 7625 | Critical |
5567 | P11 | Critical | 7625 | Normal |
5567 | p12 | Normal | 3631 | Normal |
5567 | p13 | Normal | 3631 | Normal |
3201 | p12 | Critical | 3631 | Normal |
3201 | p13 | Critical | 3631 | Normal |
3201 | p14 | Critical | 3631 | Critical |