topic Re: Vlookup in DAX Commands and Tips

Vlookup

jimpatel — Wed, 21 Sep 2022 13:38:37 GMT

Hi,

Thanks for looking at my post.

I have 2 tables named as table 1 and table 2 and unfortunately i cant link those two tables because repeatation of part numbers. Issue is in table 1, i wanted to include any DAX formula to if partnumber in table 1 match with in table 2 and write the column 5 text from table 2 to table 1 please.

Any idea please

Re: Vlookup

johnt75 — Wed, 21 Sep 2022 14:24:00 GMT

You could try

col 5 text = VAR PartNumber = 'table 1'[part number] RETURN CONCATENATEX ( CALCULATETABLE ( VALUES ( 'table 2'[column 5] ), TREATAS ( { PartNumber }, 'table 2'[part number] ) ), 'table 2'[column 5], ", " )

if there are multiple different values in column 5 for the same part number, this will include them all in a comma separated list

Re: Vlookup

jimpatel — Wed, 21 Sep 2022 14:19:59 GMT

Thanks for your reply. I am getting only , as the answer. Any idea please?

Re: Vlookup

jimpatel — Wed, 21 Sep 2022 14:24:35 GMT

Thanks for your reply. I am getting only , as the answer. Any idea please?

Re: Vlookup

johnt75 — Wed, 21 Sep 2022 14:24:42 GMT

that's my fault, I forgot to include the actual value. I've edited my original post, hopefully its OK now

Re: Vlookup

jimpatel — Wed, 21 Sep 2022 14:31:33 GMT

Thats fantastic. Thanks a lot for this formula. Much appreciated.

What if instead of "if there are multiple different values in column 5 for the same part number, this will include them all in a comma separated list" , if any row have "YES" in column 5 then answer should be "YES". Is this something we can add in this DAX formula please?

Re: Vlookup

johnt75 — Wed, 21 Sep 2022 14:34:19 GMT

try

col 5 has yes = VAR PartNumber = 'table 1'[part number] RETURN IF ( "YES" IN CALCULATETABLE ( VALUES ( 'table 2'[column 5] ), TREATAS ( { PartNumber }, 'table 2'[part number] ) ), "YES", "NO" )

Re: Vlookup

jimpatel — Wed, 21 Sep 2022 14:54:51 GMT

Perfect and much appreciated again.

Sorry one last question. Similar topic.

What if i need to add similar formula for below logic please.

1. New DAX formula in the table 4.

2. Where column 1 and Column 3 will have part numbers sometimes repeated partnumbers in both columns

3. column 5 is the desired solution. If column 1 part number or column 3 part number have "Yes" in column 5 then for the same part numbers in both column 1 and column 3 it should be "YES" please.

Sorry and any help will be massive push for my BI

Thanks a lot

Re: Vlookup

johnt75 — Wed, 21 Sep 2022 15:05:09 GMT

If I understand you correctly you want to apply the same base logic as previously but only when column 1 and column 3 have the same part number ? You can try

new column = IF ( 'table 1'[column1] = 'table 1'[column3], VAR PartNumber = 'table 1'[column1] RETURN IF ( "YES" IN CALCULATETABLE ( VALUES ( 'table 2'[column 5] ), TREATAS ( { PartNumber }, 'table 2'[part number] ) ), "YES", "NO" ), "NO" )

Re: Vlookup

jimpatel — Wed, 21 Sep 2022 15:36:44 GMT

Sorry for my poor explanation.

Actually no.

Example:

I have table 4. In table 4 i have multiple columns and in column 1 having part numbers and column 3 have sub part numbers. In some cases, column1 part number might be repeating in column 3 and vice versa.

What i am after is, if column 5 is "Yes" for either column 1 partnumber or column 3 part number, then it should be "YES" for all similar partnumbers. I hope this will help. This will help to remove the barrier what i am having.

Thanks again

Re: Vlookup

johnt75 — Wed, 21 Sep 2022 15:46:55 GMT

I think this might do it

col 5 has yes = VAR PartNumbers = { 'table 4'[column 1], 'table 4'[column 3] } RETURN IF ( "YES" IN CALCULATETABLE ( VALUES ( 'table 2'[column 5] ), TREATAS ( PartNumbers, 'table 2'[part number] ) ), "YES", "NO" )

Re: Vlookup

jimpatel — Wed, 21 Sep 2022 16:22:56 GMT

Sorry i think this will explain bit clear

Sorry again

Re: Vlookup

johnt75 — Thu, 22 Sep 2022 08:49:40 GMT

Judging by the entry for 44, you're looking for some sort of recursion, which DAX doesn't do. You could maybe look into building a path hierarchy, or in some other way restructuring the model so that you can directly access the values.

Re: Vlookup

jimpatel — Thu, 22 Sep 2022 08:54:54 GMT

thats cool idea. I will try that. Thanks again for your help.