Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |