cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeromeC Frequent Visitor
Frequent Visitor

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

Hi Everyone,

 

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

 

tab A 

KEYFiscal YearContracted?
12017 
22018 

tab B

KeyFY17FY18
1yesyes
2noyes

 

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,

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

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

HI @JeromeC

 

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

Unpivot.JPG 

Thanks

Raj

Super User
Super User

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

Hi @JeromeC

 

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

 

4 REPLIES 4
Highlighted
Super User
Super User

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

HI @JeromeC

 

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

Unpivot.JPG 

Thanks

Raj

JeromeC Frequent Visitor
Frequent Visitor

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

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 @rajendran

Super User
Super User

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

Hi @JeromeC

 

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

 

JeromeC Frequent Visitor
Frequent Visitor

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

Thanks @rajendran worked like a charm