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 Everybody,
I am having trouble making the formula work for looking up a single exchange rate value based on three filters with the enclosed table.
Currency Year Version Type Month ExchRate MonthID
USD/JPY | 151P | CURRENT | GL_AVGRATE | APR | 105 | 151PAPR |
USD/JPY | 151P | CURRENT | GL_AVGRATE | MAY | 105 | 151PMAY |
USD/JPY | 151P | CURRENT | GL_AVGRATE | JUN | 105 | 151PJUN |
USD/JPY | 151P | CURRENT | GL_AVGRATE | JUL | 105 | 151PJUL |
USD/JPY | 151P | CURRENT | GL_AVGRATE | AUG | 105 | 151PAUG |
USD/JPY | 151P | CURRENT | GL_AVGRATE | SEP | 105 | 151PSEP |
USD/JPY | 151P | CURRENT | GL_AVGRATE | OCT | 105 | 151POCT |
USD/JPY | 151P | CURRENT | GL_AVGRATE | NOV | 105 | 151PNOV |
USD/JPY | 151P | CURRENT | GL_AVGRATE | DEC | 105 | 151PDEC |
USD/JPY | 151P | CURRENT | GL_AVGRATE | JAN | 105 | 151PJAN |
USD/JPY | 151P | CURRENT | GL_AVGRATE | FEB | 105 | 151PFEB |
USD/JPY | 151P | CURRENT | GL_AVGRATE | MAR | 105 | 151PMAR |
My formula is as follows:
CALCULATE(sum(Curr_Rate[ExchRate]),Filter(Curr_Rate,Curr_Rate[Version]="CURRENT" && Curr_Rate[MonthID]="151PSEP" && Curr_Rate[Currency]="USD/JPY"))
Any assiatance would be appreciated.
Solved! Go to Solution.
works fine for me. see attached. change source value for 151PSEP and it'll reflect in the measure
works fine for me. see attached. change source value for 151PSEP and it'll reflect in the measure
chirayuw thank you!
the syntax you posted is correct, as long as the combination of these 3 columns returns only 1 row - is that the case? or does the measure not work as intended?
or do you want this to be dynamic based on the slicer selection rather than hardcoded?
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |