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 )
tamerj1
Community Champion
Community ChampionAnonymous
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.
- tamerj1
Anonymous
It seems I misunderstood tour requirement. I will modify the formula
- tamerj1
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!