How to fill the values in lookup value column

[Anonymous]

i have two tables  Confirmation_Advance, Risk Deliveries .  both values have similar values . so i created  lookup functions based confiremed , Not Confiremed. after lookup i want to fill  all blank values as a Not Confiremed.  

here i am pasted sample and   measures

Confirmation_Advance table:

PO Num        Mat            Plant             PO&mat         Confiremd

450               40.1A           RS7Z            45040.1A        Confiremed

452               40.1B           RS7Z            45040.1B         Confiremed

450               40.1C           LT01            45040.1C        Not Confiremed

455               40.1D          CHO4           45040.1H         Confiremed

458               40.1F           MT01            45040.1M       Not Confiremed

462               40.1G           RS7Z            45040.1K         Confiremed

i have created two calculated column  

Confirmed table = CALCULATETABLE( GROUPBY(Confirmations_Advance,Confirmations_Advance[PO & Mat],Confirmations_Advance[Confirmed]),FILTER(Confirmations_Advance,Confirmations_Advance[Confirmed] = "Confirmed"))

Not Confirmed table = CALCULATETABLE( GROUPBY(Confirmations_Advance,Confirmations_Advance[PO & Mat],Confirmations_Advance[Confirmed]),FILTER(Confirmations_Advance,Confirmations_Advance[Confirmed] = "Not Confirmed"))

 I have another table name called Risk Deliveries 

PO Num        Mat            Plant             PO&mat         

450               40.1A           RS7Z            45040.1A        

452               40.1B           RS7Z            45040.1B         

450               40.1C           LT01            45040.1C        

455               40.1D          CHO4           45040.1H         

458               40.1F           MT01            45040.1M       

462               40.1G           RS7Z            45040.1K       

455               40.1D          CHO4           45040.1H         

478               40.1F           MT01            45040.1S      

482               40.1G           RS7Z            45040.1G   

i have created new calculated column in  Risk Deliveries  for lookup  

combi confirmation = 'Risk deliveries'[PO Num] & 'Risk deliveries'[PO&mat ]

Now i have created New lookup  column in Risk delivers sheet

Confirmation =

var confiremed = LOOKUPVALUE('Confirmed table'[Confirmed],'Confirmed table'[PO & Material],'Risk deliveries'[combi confirmation])

var not_confirmed = LOOKUPVALUE('Not Confirmed table'[Confirmed],'Not Confirmed table'[PO & Material],'Risk deliveries'[combi confirmation])

return if(confiremed = "",not_confirmed,confiremed)

My Risk Devlieries table looks like

PO Num        Mat            Plant             PO&mat         Confirmation

450               40.1A           RS7Z            45040.1A        Confiremed

452               40.1B           RS7Z            45040.1B         Confiremed

450               40.1C           LT01            45040.1C        Not Confiremed

455               40.1D          CHO4           45040.1H          Confiremed

458               40.1F           MT01            45040.1M      Not Confiremed

462               40.1G           RS7Z            45040.1K          Confiremed

455               40.1D          CHO4           45040.1H                                 --->  want to fill all blank place " Not Confiremed"

478               40.1F           MT01            45040.1S      

482               40.1G           RS7Z            45040.1G   

 i want to fill Not Confiremed in blank place in confirmation  column. 

looking for support . thanks in advance .

[AlexisOlson]

I think you can do this all in one step without needing the concatenated columns or calculated tables.

Try this as a new calculated column on Risk Deliveries.

Confirmation =
LOOKUPVALUE (
    Confirmation_Advance[Confiremd],
    Confirmation_Advance[PO Num], 'Risk deliveries'[PO Num],
    Confirmation_Advance[Mat], 'Risk deliveries'[Mat],
    "Not Confiremd"
)

[Anonymous]

@AlexisOlson  Thank you so much 

[amitchandak · Accepted solution]

@Anonymous , Do this change in last return

return Switch(true(),

isblank(confirmed ), "Not Confiremed"

confiremed = ""  ,not_confirmed,confiremed)

or

return if(confiremed = "" || isblank(confirmed ) ,not_confirmed,confiremed)

[Anonymous]

@amitchandak  Thank you so much  its work