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
Table2Account 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.
OutputID
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
Table2Account 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
table2Account 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 isID
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
