Forum Discussion
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...
Anonymous
Please try
= IF ( TableName[ID] IN VALUES ( TableName[L] ) && TableName[ID] IN VALUES ( TableName[F] ), 1, 0 )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 )
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.
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
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.
Anonymous
It seems I misunderstood tour requirement. I will modify the formula
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 )Thank you really appreciated it!