Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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
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
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
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
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
Thanks @Anonymous worked like a charm
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |