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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Get unaggregated value of a table with DAX measure

Hello everyone,

 

I am working with a historic currency table which has the following columns:
    Currency, ExchangeRate, ExchangeRateDate. 

 

I am trying to get a DAX measure which would give me the oldest currency exchange rate for each currency. When I say the oldest, I mean the oldest value that is defined by my date slicer in the report.  This date can be modified by the user via the slicer, therefore I cannot use a calculated column or table but I need to do it in a DAX measure. 


For example, if my slicer is set between 2018/12/31 and 2019/12/31, I would like to get the exchange rates on the 2018/12/31.  Below is the result I would like to obtain (it should be in a measure which will be used in another measure afterwards).

 
CurrencyExchangeRateDate
GBP0.6631/12/2018
INR0.7731/12/2018
USD0.8831/12/2018

 

To give a more concrete situation, I need to add a column with this value to this table:

eg2.PNG


I tried using the CALCULATE function, but it requires me to use an aggregation function, which I do not need, and when I use eg. MAX it returns the highest exchange rate accross the different currencies. 

The relation model between my dimensions and facts are:

  • DimCalendar[Date] -> ExchangeRate[Date] (1 to many)
  • DimCurrency[Currency] -> DimSecurity[Currency] (1 to many)
  • DimCurrency[Currency] -> ExchangeRate[Currency] (1 to many)

 

I find it weird that I cannot "simply" do the equivalent of the follow SQL query in DAX 

 

select *

from ExchangeRate as er

join DimCalendar as dc on er.Date = dc.Date

where er.Date = min(dc.Date)

;

 

I am pretty sure my problem is context related but I can't quite get it.

Any help would be terrific. Please let me know if I did not provide enough information.

 

Best regards,

Thomas

2 REPLIES 2
v-xuding-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please try this:

 

 

Measure = 
var mindate = MIN(DimCalendar[Date])
return
CALCULATE(MAX(ExchangeRate[ExchangeRate]),FILTER(ExchangeRate,ExchangeRate[Date] = mindate))

 

 

v-xuding-msft_0-1599115966913.png

 

It will show maximum value by default. If you want to show total values, please try this:

Measure 2 = 
var mindate = MIN(DimCalendar[Date])
return
CALCULATE(SUM(ExchangeRate[ExchangeRate]),FILTER(ExchangeRate,ExchangeRate[Date] = mindate))

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@Anonymous , Not very clear . but you have use a filter like in measure or calculatetable

filter(ExchangeRate , ExchangeRate[Date] = min(DimCalendar[Date]))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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