topic Re: vlookup in DAX Commands and Tips

vlookup

PhilippeMuniesa — Wed, 09 Nov 2022 19:44:43 GMT

hello,

(from france)

I want to identify in a new column (powerpivot with DAX) the values that exist in column A and in column B.

col A col B newCol

1 null 1

2 1 1

3 null null

4 5 5

5 null 5

Thank you for your help

Philippe

Re: vlookup

Greg_Deckler — Wed, 09 Nov 2022 20:09:23 GMT

PhilippeMuniesa Try:

newCol = VAR __B = SELECTCOLUMNS(ALL('Table'),"__C",[B]) VAR __A = SELECTCOLUMNS(ALL('Table'),"__C",[A]) VAR __AinB = IF([col A] IN __B,[col A],BLANK()) VAR __BinA = IF([col B] IN __A,[col B],BLANK()) VAR __Return = MAXX(FILTER(UNION(__AinB, __BinA),[__C] <> BLANK()),[__C]) RETURN __Return

Re: vlookup

PhilippeMuniesa — Wed, 09 Nov 2022 20:54:35 GMT

thank you, but problème with french traduction , as ; and error with __

Thank you

philippe

Re: vlookup

PhilippeMuniesa — Wed, 09 Nov 2022 22:49:10 GMT

it seems that the IN operator is not operational in Powerpivot would there be another solution? thank you

philippe

Re: vlookup

AlexisOlson — Thu, 10 Nov 2022 00:41:22 GMT

I think PowerPivot allows CONTAINS.

I'm not completely sure I understand your logic but try this:

newCol = IF ( ISBLANK ( 'Table'[ColB] ), IF ( CONTAINS ( 'Table', 'Table'[ColB], 'Table'[ColA] ), 'Table'[ColA] ), 'Table'[ColB] )

Re: vlookup

AlexisOlson — Thu, 10 Nov 2022 00:51:45 GMT

I had trouble following what this is trying to do, but it did inspire this solution:

newCol = VAR _ColB = VALUES ( 'Table'[ColB] ) VAR _CurrRow = { 'Table'[ColA], 'Table'[ColB] } RETURN MAXX ( INTERSECT ( _ColB, _CurrRow ), 'Table'[ColB] )

Re: vlookup

PhilippeMuniesa — Thu, 10 Nov 2022 18:15:57 GMT

thank you,

It' s ok,

in the meantime, I tried a solution similar to yours,

"for english must replace ; by ,"

=if(Ta_Base[Siren]=Blank();blank();if(LOOKUPVALUE([Numéro SIREN de la société holding tête de groupe française];[Siren]; [Siren]) ;

[Numéro SIREN de la société holding tête de groupe française]&"-F-";

if(CONTAINS(Ta_Base;[Numéro SIREN de la société holding tête de groupe française];[Siren]);

[Siren]& "-M-"

;BLANK())

)

which I also post with an excel file containing the table, and the dax part in powerpivot for members who are interested.

a big thank you to you

https://www.dropbox.com/t/7P1YZjrKZJ3TSs3q

Philippe