Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Vlookup using same column for search value and column value

Hi everyone,   I cant get my head around this problem in Power BI. I´m adding a new column to my table and want to check lookup if the same ID has value in both the column "F" and "L", but on diffe...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 

    Please try

    =
IF (
    TableName[ID]
        IN VALUES ( TableName[L] )
            && TableName[ID] IN VALUES ( TableName[F] ),
    1,
    0
)
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    Please use

    =
VAR CurrentIDTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR FValue =
    SUMX ( CurrentIDTable, TableName[F] )
VAR LValue =
    SUMX ( CurrentIDTable, TableName[L] )
RETURN
    IF ( FValue > 0 && LValue > 0, 1, 0 )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi! Thanks, but unfortunalety it doesn´t work. One thing that might add more complexity is that a ID can have multiple rows with values in column "F" and "L", but shouldn affect your code above?

 

edit: Is the problem related to the value is not 1 in both "F" and "L" on the same row?

 

IDFLBoth F and L
1101
1011
1101
1011
1011
1011

 

 

 

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

Anonymous 
This is the result I get out of this code

Am I missing something?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

     

    No, it looks correct! Can there be some issue with the format of my table? The columns "F" and "L" are calculated columns in the datatype and format "Whole number". The ID columns is not calculated but has the same format and datatype. 

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

      Anonymous 
      Try either

      =
IF (
    TableName[ID]
        & ""
            IN VALUES ( TableName[L] )
                && TableName[ID] IN VALUES ( TableName[F] ),
    1,
    0
)

      OR

      =
IF (
    VALUE ( TableName[ID] )
        IN VALUES ( TableName[L] )
            && TableName[ID] IN VALUES ( TableName[F] ),
    1,
    0
)

      Otherwise check for hidden charactors or spaces. Try to clean the column in PQ

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi! I did some testing with sample ID. And the dax is only working when I put ID as 1 and not for example 5 or 1XXXX as some ID contains more than one number.