Measure to getting value from table using multiple variables

luirib

Board Regular
Joined
Sep 21, 2005
Messages
56
I need to write a DAX measure that will pull the rate from the below table (ER). My report will have a date and an entity, that will determine what rate is to be picked. For example, if I pick eas (see entities table below) and my month is Jan., the measure should show 60.333.

I think that the LOOKUPVALUE is intended for calculated columns only. Should I go CALCULATE(SUM(ER[Rate]), FILTER... [and here is where I get lost. I know that I need to include the country as a filter so it knows what conversion to use].

Thanks a lot.
This is the ER table
1588909299488.png

This is the Entities table
1588909705428.png
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,240
Power Pivot is a model based tool. You build a model and then use it to extract the data. Join the entities[orcurr] to er[to]
you can then put date and code in a matrix with a simple measure like AVERAGE(er[rate])
 

Watch MrExcel Video

Forum statistics

Threads
1,114,653
Messages
5,549,213
Members
410,905
Latest member
Extjel
Top