topic Re: Help with Vlookup using same column for search value and column value in DAX Commands and Tips

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

Anonymous — Sun, 12 Jun 2022 19:12:33 GMT

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

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

tamerj1 — Sun, 12 Jun 2022 18:40:47 GMT

Anonymous

Ok but why the result for the first ID 1 is 0?

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

Anonymous — Sun, 12 Jun 2022 19:12:59 GMT

Sorry, misstake from me

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

tamerj1 — Sun, 12 Jun 2022 19:16:54 GMT

Anonymous

Please try

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

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

Anonymous — Mon, 13 Jun 2022 10:37:38 GMT

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?

ID	F	L	Both F and L
1	1	0	1
1	0	1	1
1	1	0	1
1	0	1	1
1	0	1	1
1	0	1	1

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

tamerj1 — Mon, 13 Jun 2022 10:48:03 GMT

Anonymous
This is the result I get out of this code

Am I missing something?

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

Anonymous — Mon, 13 Jun 2022 10:56:41 GMT

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.

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

tamerj1 — Mon, 13 Jun 2022 11:00:34 GMT

Anonymous
Try either

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

= 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

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

Anonymous — Tue, 14 Jun 2022 07:16:04 GMT

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.

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

tamerj1 — Tue, 14 Jun 2022 07:44:39 GMT

Anonymous

It seems I misunderstood tour requirement. I will modify the formula

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

tamerj1 — Tue, 14 Jun 2022 13:00:56 GMT

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 )

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

Anonymous — Thu, 16 Jun 2022 06:14:58 GMT

Thank you really appreciated it!