How do I do a IF statement and Vlookup together in PowerBI ?

[Anonymous]

Hi Everyone,

In excel, I have two tabs, as below..

tab A 

KEY	Fiscal Year	Contracted?
1	2017
2	2018

tab B

Key	FY17	FY18
1	yes	yes
2	no	yes

In tab A, contracted I want to do a IF statement if "Fiscal Year" is 2017, look up in tab B from column "FY17",  if "Fiscal Year" is 2018, look up in tab B from Column "Fy18".

This is easy in Excel but How can I do this in PowerBI desktop ? 

Thank you in advance,

[Anonymous · Accepted solution]

HI @Anonymous

There may be other smart way of doing this. 

This is what i have tried:

1. In power Query -> selected Key column in Tab B and did' Unpivot other columns'

2. Created calculated column in Tab A

Contracted =
VAR inFY= CONCATENATE("FY",RIGHT(TabA[Fiscal Year],2)) RETURN
CALCULATE(MIN(TabB[Value]),FILTER(TabB,TabB[Key]=TabA[Key]&&TabB[Attribute]=inFY))

 

Thanks

Raj

[Anonymous · Accepted solution]

HI @Anonymous

There may be other smart way of doing this. 

This is what i have tried:

1. In power Query -> selected Key column in Tab B and did' Unpivot other columns'

2. Created calculated column in Tab A

Contracted =
VAR inFY= CONCATENATE("FY",RIGHT(TabA[Fiscal Year],2)) RETURN
CALCULATE(MIN(TabB[Value]),FILTER(TabB,TabB[Key]=TabA[Key]&&TabB[Attribute]=inFY))

 

Thanks

Raj

[Anonymous]

Thank you!  that worked, however if result is blank because tabB is not a complete list, how do I make the blank return a different value. i.e. "Does not exist" 

thank you again @Anonymous

[Anonymous · Accepted solution]

Hi @Anonymous

You can use something like this:

Example : IF( table[col1] = BLANK(),"Doesnot Exist",table[col1])...

so, in your case,

Contracted =
VAR inFY= CONCATENATE("FY",RIGHT(TabA[Fiscal Year],2)) RETURN
IF(CALCULATE(MIN(TabB[Value]),FILTER(TabB,TabB[Key]=TabA[Key]&&TabB[Attribute]=inFY))= BLANK(),"Does not exist",CALCULATE(MIN(TabB[Value]),FILTER(TabB,TabB[Key]=TabA[Key]&&TabB[Attribute]=inFY)))

Thanks

Raj

[Anonymous]

Thanks @Anonymous worked like a charm