Forum Discussion

rohitMe's avatar
rohitMe
Icon for Advocate I rankAdvocate I
8 years ago
Solved

lookup between tables

Hi all,   I have customer details and I want to look up whether a customer is listed twice. I have two tables that look as shown below:     I want to identify whether the customer is l...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi rohitMe,

     

    You can create a calculate column with below formula to lookupvalue from table2.

    Call ID from Table2 =
CONCATENATEX (
    FILTER (
        ALL ( Table2 ),
        Table2[CUSTOMER MOBILE] = EARLIER ( Table1[CUSTOMER MOBILE] )
    ),
    Table2[CALL ID],
    ","
)

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi rohitMe,

 

It seems like you want to use customer mobile and call id to check two tables and return a tag if this records are appears in both two tables, right?

 

If that is a case, you can add a calculate column to table 1 to get current row contents(CUSTOMER MOBILE,CALL ID), then use these variables to check table 2.

 

Sample:

Is Appear Twice =
VAR _cMB = Table1[CUSTOMER MOBILE]
VAR _cID = Table1[CALL ID]
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( Table2 ),
                Table2[CUSTOMER MOBILE] = _cMB
                    && Table2[CALL ID] = _cID
            )
        )
            > 0,
        "Y",
        "N"
    )

 

Regards,

Xiaoxin Sheng

rohitMe's avatar
rohitMe
Icon for Advocate I rankAdvocate I
8 years ago

Anonymous

 

Actaully instead of the tag being "Y" or "N" I want to return the Call ID from table 2 (if calculated column is created in table 1) of the record that appears in both table.

 

Also do tell me how to do this by using the customer mobile column only to check whether the record is in both the table.

 

Regards

Rohit

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi rohitMe,

     

    Try below formula:

    Is Appear Twice =
VAR _cMB = Table1[CUSTOMER MOBILE]
VAR _cID = Table1[CALL ID]
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( Table2 ),
                Table2[CUSTOMER MOBILE] = _cMB
                    && Table2[CALL ID] = _cID
            )
        )
            > 0,
        _cID
    )

    It will return 'call id' if current 'call id' also appears in another table, blank result means not have duplicate records.

     

    Regards,

    Xiaoxin Sheng

    • rohitMe's avatar
      rohitMe
      Icon for Advocate I rankAdvocate I
      8 years ago

      hi Anonymous

       

      Thanks for your solution but still it is not what I require. I guess I haven't been able to make my problem clear to you.

       

      Table 2Table 1

      I have two tables as I have shown above.

      I want to find out which entry from table 1 has been made again in table 2 but my criteria for checking the duplicates is only the customer mobile column(i.e. Only customer mobile columns is to be checked for duplicate entries).

      If there are any entries that are duplicate then I want to create a new column in table 1 in which I want to return the call id value corresponding to the duplicate entry of table 2 else it returns blank.

       

      required output of table 1

      The output shown above was achieved using the vlookup() function in excel and I require something like that in power bi.

      The ordering of the columns might be different. But the output must have the shown three columns. Also there are no duplicate values in call id column.

       

      Hope you now understand the problem clearly and are able to help me. Please ask me in case you do not understand anything.

       

      Thanks and Regards

      Rohit

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi rohitMe,

         

        You can create a calculate column with below formula to lookupvalue from table2.

        Call ID from Table2 =
CONCATENATEX (
    FILTER (
        ALL ( Table2 ),
        Table2[CUSTOMER MOBILE] = EARLIER ( Table1[CUSTOMER MOBILE] )
    ),
    Table2[CALL ID],
    ","
)

         

        Regards,

        Xiaoxin Sheng