Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NMahi1703
Frequent Visitor

Filtering Data in Another Table which has the relationship with One table with Mutiple Conditions

Hi Team,

I Have One table Name Table_1 below 

Account IDID
94841811
94843291
94844691

Another Table as Table_2

Account NumberBilliDName
94841811P1
94843291P2
9484NAP3
9484NAP4

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:

IDAccount NOName
18119484P1
32919484P3
46919484P1
46919484P2
46919484P3
46919484P4

Kindly Help Me. 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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 ()
        )
    )

1.PNG
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
NMahi1703
Frequent Visitor

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 IDID
36301811
76253291
76255567

Table2

Account NumberBilliDName
3630NAP1
3630NAP2
3630NAP3
3630NAP4
76253291P5
76253291P6
88883291P7
76254691P8
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 

IDName
1811P1
1811P2
1811P3
1811P4
3291P5
3291P6
5567P5
5567P6
5567P8
5567P9
5567P10
5567P11
v-shex-msft
Community Support
Community Support

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 ()
        )
    )

1.PNG
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.