Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

Unpivot.JPG 

Thanks

Raj

View solution in original post

Anonymous
Not applicable

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

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

Unpivot.JPG 

Thanks

Raj

Anonymous
Not applicable

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
Not applicable

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
Not applicable

Thanks @Anonymous worked like a charm

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.