vlookup

[PhilippeMuniesa]

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.

ex

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

[Greg_Deckler]

@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

[AlexisOlson]

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] )

[PhilippeMuniesa]

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

philippe

[AlexisOlson · Accepted solution]

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]
    )

[PhilippeMuniesa]

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

[PhilippeMuniesa]

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

Thank  you

philippe