cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.