Forum Discussion

jimpatel's avatar
jimpatel
Post Patron
3 years ago
Solved

Vlookup

Hi,

Thanks for looking at my post.

I have 2 tables named as table 1 and table 2 and unfortunately i cant link those two tables because repeatation of part numbers. Issue is in table 1, i wanted to include any DAX formula to if partnumber in table 1 match with in table 2 and write the column 5 text from table 2 to table 1 please.

 

Any idea please

  • jimpatel's avatar
    jimpatel
    3 years ago

    Thats fantastic. Thanks a lot for this formula. Much appreciated.

    What if instead of "if there are multiple different values in column 5 for the same part number, this will include them all in a comma separated list" , if any row have "YES" in column 5 then answer should be "YES". Is this something we can add in this DAX formula please? 

13 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could try

     

    col 5 text =
VAR PartNumber = 'table 1'[part number]
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( 'table 2'[column 5] ),
            TREATAS ( { PartNumber }, 'table 2'[part number] )
        ),
        'table 2'[column 5],
        ", "
    )

     

    if there are multiple different values in column 5 for the same part number, this will include them all in a comma separated list

    • jimpatel's avatar
      jimpatel
      Post Patron
      3 years ago

      Thanks for your reply.  I am getting only , as the answer. Any idea please?

       

       

       

  • jimpatel's avatar
    jimpatel
    Post Patron
    3 years ago

    Thanks for your reply.  I am getting only , as the answer. Any idea please?

     

     

    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      that's my fault, I forgot to include the actual value. I've edited my original post, hopefully its OK now

  • jimpatel's avatar
    jimpatel
    Post Patron
    3 years ago

    Thats fantastic. Thanks a lot for this formula. Much appreciated.

    What if instead of "if there are multiple different values in column 5 for the same part number, this will include them all in a comma separated list" , if any row have "YES" in column 5 then answer should be "YES". Is this something we can add in this DAX formula please? 

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    try 

    col 5 has yes =
VAR PartNumber = 'table 1'[part number]
RETURN
    IF (
        "YES"
            IN CALCULATETABLE (
                VALUES ( 'table 2'[column 5] ),
                TREATAS ( { PartNumber }, 'table 2'[part number] )
            ),
        "YES",
        "NO"
    )
    • jimpatel's avatar
      jimpatel
      Post Patron
      3 years ago

      Perfect and much appreciated again.

       

      Sorry one last question. Similar topic. 

      What if i need to add similar formula for below logic please.

      1. New DAX formula in the table 4.

      2. Where column 1 and Column 3 will have part numbers sometimes repeated partnumbers in both columns

      3. column 5 is the desired solution. If column 1 part number or column 3 part number have "Yes" in column 5 then for the same part numbers in both column 1 and column 3 it should be "YES" please.

       

      Sorry and any help will be massive push for my BI

       

      Thanks a lot

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        If I understand you correctly you want to apply the same base logic as previously but only when column 1 and column 3 have the same part number ? You can try

        new column =
IF (
    'table 1'[column1] = 'table 1'[column3],
    VAR PartNumber = 'table 1'[column1]
    RETURN
        IF (
            "YES"
                IN CALCULATETABLE (
                    VALUES ( 'table 2'[column 5] ),
                    TREATAS ( { PartNumber }, 'table 2'[part number] )
                ),
            "YES",
            "NO"
        ),
    "NO"
)