Forum Discussion

krishna_murthy's avatar
krishna_murthy
Helper II
1 year ago
Solved

Inner Join count rows

Hi Team,

 

Please find the below table and do the

Table1:
ID, Name
1, A
1, B
 table 2:
ID, Location
1,D
1,E
2,F

 

What is the Inner join count rows?

what is the left outer join count rows ?

 

 

Need a help
  • ashleyfiore's avatar
    ashleyfiore
    1 year ago
    Inner Join Row Count :=
COUNTROWS (
    GENERATE(
        Table1,
        FILTER(
            Table2,
            Table2[ID] = Table1[ID]
        )
    )
)

     

    Left Outer Join Row Count :=
COUNTROWS (
    GENERATE (
        Table1,
        VAR MatchingRows =
            FILTER (
                Table2,
                Table2[ID] = Table1[ID]
            )
        RETURN
            IF (
                COUNTROWS(MatchingRows) > 0,
                MatchingRows,
                ROW ( "ID", BLANK(), "Location", BLANK() )  -- simulate left join with blanks
            )
    )
)

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    krishna_murthy 

     

    An inner join will return only the rows where there is a match in both tables based on the ID column.

    ID, Name, Location
    1, A, D
    1, A, E
    1, B, D
    1, B, E

     

    A left outer join will return all rows from Table1 and the matched rows from Table2. If there is no match, the result is NULL on the side of Table2.

    ID, Name, Location
    1, A, D
    1, A, E
    1, B, D
    1, B, E

  • ashleyfiore's avatar
    ashleyfiore
    Advocate IV
    1 year ago
    Inner Join Row Count :=
COUNTROWS (
    GENERATE(
        Table1,
        FILTER(
            Table2,
            Table2[ID] = Table1[ID]
        )
    )
)

     

    Left Outer Join Row Count :=
COUNTROWS (
    GENERATE (
        Table1,
        VAR MatchingRows =
            FILTER (
                Table2,
                Table2[ID] = Table1[ID]
            )
        RETURN
            IF (
                COUNTROWS(MatchingRows) > 0,
                MatchingRows,
                ROW ( "ID", BLANK(), "Location", BLANK() )  -- simulate left join with blanks
            )
    )
)
  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    Hi krishna_murthy,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you ashleyfiore, FBergamaschi abnd bhanu_gautam for the prompt response.

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's resolved your issue? or let us know if you need any further assistance.

    Thanks and regards,
    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Community Support
      1 year ago

      Hi @krishna_murthy,

       

      We wanted to kindly follow up to check if the solution provided by the user's resolved your issue? or let us know if you need any further assistance.

      Thanks and regards,
      Anjan Kumar Chippa

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    1 year ago

    You are only asking the result or you want the DAX code to create the tables?

  • v-kathullac's avatar
    v-kathullac
    Community Support
    1 year ago

    Hi @krishna_murthy,

     

    We wanted to kindly follow up to check if the solution provided by the user's resolved your issue? or let us know if you need any further assistance.

    Thanks.