Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
I Have One table Name Table_1 below
Account ID | ID |
9484 | 1811 |
9484 | 3291 |
9484 | 4691 |
Another Table as Table_2
Account Number | BilliD | Name |
9484 | 1811 | P1 |
9484 | 3291 | P2 |
9484 | NA | P3 |
9484 | NA | P4 |
I have the realtion between Table1 to Table2 with Account Id and Account Number.
My requirment is if Id in table is maching with Billid in Table2 then that prodcut has to display.If There is no Match found then Need to display all the Name's in Table
Output:
ID | Account NO | Name |
1811 | 9484 | P1 |
3291 | 9484 | P3 |
4691 | 9484 | P1 |
4691 | 9484 | P2 |
4691 | 9484 | P3 |
4691 | 9484 | P4 |
Kindly Help Me.
Solved! Go to Solution.
HI @NMahi1703,
You can create a new table with crossjoin function with table 1 field and table 2 Name fields, then add a filter to remove not match records based on the raw table records mapping.
Expand =
VAR idList =
CALCULATETABLE (
VALUES ( Table2[BilliD] ),
FILTER ( Table2, [BilliD] <> "NA" )
)
VAR mapping =
SELECTCOLUMNS (
FILTER ( Table2, [BilliD] <> "NA" ),
"Merged",
[Account Number] & "-" & [BilliD] & "-" & [Name]
)
RETURN
FILTER (
CROSSJOIN ( Table1, VALUES ( Table2[Name] ) ),
IF (
[ID] IN idList,
[Account ID] & "-" & [ID] & "-" & [Name] IN mapping,
TRUE ()
)
)
Regards,
Xiaoxin Sheng
Hi @v-shex-msft ,
Thanks for you help , small change in the condition and data could you please help me out with below senario.
Table1
Account ID | ID |
3630 | 1811 |
7625 | 3291 |
7625 | 5567 |
Table2
Account Number | BilliD | Name |
3630 | NA | P1 |
3630 | NA | P2 |
3630 | NA | P3 |
3630 | NA | P4 |
7625 | 3291 | P5 |
7625 | 3291 | P6 |
8888 | 3291 | P7 |
7625 | 4691 | P8 |
7625 | P9 | |
7625 | P10 | |
7625 | P11 |
Now here as mentioned above account id and Account Number has an many to many relationship.Desiried output is
ID | Name |
1811 | P1 |
1811 | P2 |
1811 | P3 |
1811 | P4 |
3291 | P5 |
3291 | P6 |
5567 | P5 |
5567 | P6 |
5567 | P8 |
5567 | P9 |
5567 | P10 |
5567 | P11 |
HI @NMahi1703,
You can create a new table with crossjoin function with table 1 field and table 2 Name fields, then add a filter to remove not match records based on the raw table records mapping.
Expand =
VAR idList =
CALCULATETABLE (
VALUES ( Table2[BilliD] ),
FILTER ( Table2, [BilliD] <> "NA" )
)
VAR mapping =
SELECTCOLUMNS (
FILTER ( Table2, [BilliD] <> "NA" ),
"Merged",
[Account Number] & "-" & [BilliD] & "-" & [Name]
)
RETURN
FILTER (
CROSSJOIN ( Table1, VALUES ( Table2[Name] ) ),
IF (
[ID] IN idList,
[Account ID] & "-" & [ID] & "-" & [Name] IN mapping,
TRUE ()
)
)
Regards,
Xiaoxin Sheng
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |