Forum Discussion

Ricardo77's avatar
Ricardo77
Icon for Helper II rankHelper II
4 years ago
Solved

Find columns from one table in another table

Hello,

i'm trying to find columns of 1 table on a 2nd table, but i'm struggling because i can't seem to use distinct or values with text operators (2nd column can be slightly different, as you'll see), and i also was trying to use addcolumns and summarize in a variable but i don't know how to use it after on the Calculate's Filter.

So what i pretend is to count table1\column1 in table2\column1, the detail is that :

- it could not exist, as AA-00004

- it can exist exactly equal or with a variation on the string (AA-00001 with AA-00001 and AA-00001--1)











What's the best / correct way to do this?
Thanks in advance
Tip
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 

    Then use

     

    =
SUMX (
    VALUES ( table1[Column1] ),
    IF (
        SUMX (
            VALUES ( table2[Column1] ),
            IF ( CONTAINSSTRING ( table2[Column1], table1[Column1] ), 1 )
        ) > 0,
        1
    )
)

     

9 Replies

  • Ricardo77's avatar
    Ricardo77
    Icon for Helper II rankHelper II
    4 years ago

    Thanks for your help tamerj1 , it seems OK in my real PBIx, by some reason in the PBI POC i created, with the 2 tables directly created in PBI, i was getting that message error.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Ricardo77 

    you can try

    =
SUMX (
    VALUES ( table2[Column1] ),
    SUMX (
        VALUES ( table1[Column1] ),
        IF ( CONTAINSSTRING ( table2[Column1], table1[Column1] ), 1 )
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello tamerj1  , thanks for your help.

      I think it's working but just failing in one detail: i need it to count on table1, so when values are found they just are counted once , as AA-00001 and AA-00005 .

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello tamerj1 ,

    i was trying now to do something more similar to you, as the idea is just to count once, but:
    1) shouldn't the 1st SUMX be done over Table1, as we want to count from there?

    2) can't i simply create a measure with the code you kindly shared and drag it to the screen? An error is appearing.


    Thanks and Regards

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      The first Values is table1 the 2nd values is table2. Please copy the same code in my last reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    i was trying now to do something more similar to you, as the idea is just to count once, but:
    1) shouldn't the 1st SUMX be done over Table1, as we want to count from there?

    2) can't i simply create a measure with the code you kindly shared and drag it to the screen? An error is appearing.

     


    Thanks and Regards