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 different rows.
For example: The added column "Both F and L" should check if the same ID in the table has a value in both "F" and the "L" column, and if the same ID exist in the the table with the value "1" in both "F" and "L" then return 1 in the "Both F and L" column. I want to check if the customer has bought both the F and L product, but in different time periods.
My problem is I cant get the vlookup to work when using the same column for evertyhing (search value and search column). Dont want to create a new table for this.
| ID | F | L | Both F and L |
| 1 | 1 | 0 | 1 |
| 2 | 0 | 1 | 0 |
| 3 | 1 | 0 | 0 |
| 1 | 0 | 1 | 1 |
Regards,
Niclas
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 )
