Efficient DAX for Dynamic FX Rate LookupDate by Drill-Level & Depth Slicer on a 40M-Row Model

[Digger]

Hello everyone,

I’m working on a Power BI report with about 40 million rows and I need some help designing three DAX measures to fetch FX rates in a matrix visual. I’d like to keep the existing one-directional relationships (Calendar → I03_DKD → PP_Currency_rate) intact for performance reasons, and ensure these measures run efficiently.

Problem Statement

I have a Power BI matrix that shows currency‐denominated transactions broken down by date (Year → Month → Day) and by currency code. I need three related FX‐rate measures—Daily, MonthEnd and SelectedEndDate—each of which must:

1. Dynamically choose the “lookup date” (LookupDate) based on both:

   • Your Depth slicer selection (Daily / MonthEnd / SelectedEndDate), and

   • The current drill level in the matrix:

     • If you’re drilled down to transactions, use the transaction’s own date.

     • If you’re at the Day level, use that day.

     • If you’re at the Month level, use the last day of that month.

     • If you’re at the Year level, use the last day of that year (but never in the future; clamp to TODAY()).

2. Respect filters on your Calendar (so that MAX(Calendar[Date]) automatically honors the current DBName and currency‐code filters) and also handle periods with no transactions.

3. Fetch the “last available” FX rate from your rate table (PP_Currency_rate), finding the single rate record where:

   • name_2 = current currency code, and

   • date ≤ LookupDate,

   • then picking the newest of those dates (TOPN(1, … date DESC) or equivalent).

4. Only display a rate on rows where a currency code is in context (i.e. at the currency or transaction level), and return blank everywhere else (e.g. on pooled DBName totals).

---

Desired Outcome

• FX Rate Daily shows, for each matrix cell, the FX rate on exactly that row’s date (transaction date or calendar day, or period‐end when collapsed).

• FX Rate MonthEnd always shows the rate at the end of the visible period (e.g. month‐end even at the Day level).

• FX Rate SelectedEndDate always shows the rate on the single date chosen by your Calendar slicer (clamped to today), ignoring the drill hierarchy.

This logic must yield exactly the results illustrated in your Excel mockup for every combination of drill level and slicer setting.

Data model & requirements

• Transactions table: I03_DKD

  • Transaction date: I03_DKD[I02_OP_DATA.1]

  • Currency code: I03_DKD[N01_ACCT.N01_KODAS_VL]

• Calendar table: Calendar[Date] with hierarchy Year → Month → Day.

• FX rates table: PP_Currency_rate

  • Currency code: PP_Currency_rate[name_2]

  • Rate date: PP_Currency_rate[date]

  • Rate value: PP_Currency_rate[amount_2]

• Depth slicer: CurrencyDepth[Depth] with values

  • "Daily"

  • "MonthEnd"

  • "SelectedEndDate"

I need to build three separate measures:

1. FX Rate Daily

2. FX Rate MonthEnd

3. FX Rate SelectedEndDate

Each measure must:

1. Determine a lookup date (LookupDate) dynamically based on:

   • Matrix drill level:

     • If drilled into transactions → use the transaction date.

     • If at Day level → use that day.

     • If at Month level → use the last day of that month.

     • If at Year level → use the last day of that year (but never a future date).

   • Depth slicer selection:

     • For Daily, lookup = the date above.

     • For MonthEnd, lookup = month-end of that date.

     • For SelectedEndDate, lookup = the date chosen in the calendar slicer (clamped to ≤ TODAY()).

2. Respect all existing filters:

   • The Calendar → I03_DKD relationship is single-directional, so MAX(Calendar[Date]) isn’t picking up filters on DBName or currency code by default. I need a pattern that honors both the drill-through filters and any slicers without switching my model to bidirectional.

3. Fetch the last available FX rate:

   • Find the record in PP_Currency_rate where

     name_2 = current currency code  
       && date ≤ LookupDate  

   • Return the rate for the latest date (using TOPN(1, … date DESC) or equivalent).

4. Show the measure only at currency/transaction levels; return blank at summary levels (DBName or higher).

Expected behavior (based on my Excel mock-up)

Matrix drilled to Day level (e.g. 2025-01-05)

Row FX Rate Daily FX Rate MonthEnd FX Rate SelectedEndDate

USD (header)	rate @01-05	rate @01-31	rate @slicer date
2020-10-29	rate @10-29	rate @10-31	rate @slicer date
2019-08-07	rate @08-07	rate @08-31	rate @slicer date

Matrix collapsed to Month level (January 2025)

Row FX Rate Daily FX Rate MonthEnd FX Rate SelectedEndDate

USD (header)	rate @01-31	rate @01-31	rate @slicer date
2020-10-29	rate @10-29	rate @10-31	rate @slicer date
…	…	…	…

Matrix collapsed to Year level (2025)

Row FX Rate Daily FX Rate MonthEnd FX Rate SelectedEndDate

USD (header)	rate @12-31	rate @12-31	rate @slicer date
2020-10-29	rate @10-29	rate @10-31	rate @slicer date
…	…	…	…

What I’ve tried

• SWITCH(TRUE(), ISINSCOPE(...), …) patterns combined with TOPN + MAXX.

• Using CROSSFILTER inside measures to force Calendar filtering.

• LOOKUPVALUE for exact dates, but that fails when there’s no rate record on a given day.

• Multiple nested IF/BLANK() checks, which end up returning blanks or mis-clamped dates.

None of these have given me a single, compact pattern that:

• Always chooses the correct LookupDate across all drill levels and slicer settings.

• Honors my existing single-direction relationships.

• Executes quickly on a 40 million-row model.

---

Request for help

Could you please share your best-practice DAX patterns or complete measure definitions for these three scenarios, ensuring:

• Minimal branching (no excessive IF/BLANK() logic).

• Proper filter propagation on Calendar[Date] without changing model relationships.

• Efficient rate lookup (TOPN + date filter or CALCULATE(MAX, …)).

• Scalability to large datasets (40 M+ rows).

Any code snippets or performance tips would be greatly appreciated—thank you in advance!

PBIX test attached https://limewire.com/d/o6cTZ#olY43xItQN

[Digger]

Hello everyone,

I’m working on a Power BI report with about 40 million rows and I need some help designing three DAX measures to fetch FX rates in a matrix visual. I’d like to keep the existing one-directional relationships (Calendar → I03_DKD → PP_Currency_rate) intact for performance reasons, and ensure these measures run efficiently.

Problem Statement

I have a Power BI matrix that shows currency‐denominated transactions broken down by date (Year → Month → Day) and by currency code. I need three related FX‐rate measures—Daily, MonthEnd and SelectedEndDate—each of which must:

1. Dynamically choose the “lookup date” (LookupDate) based on both:

   • Your Depth slicer selection (Daily / MonthEnd / SelectedEndDate), and

   • The current drill level in the matrix:

     • If you’re drilled down to transactions, use the transaction’s own date.

     • If you’re at the Day level, use that day.

     • If you’re at the Month level, use the last day of that month.

     • If you’re at the Year level, use the last day of that year (but never in the future; clamp to TODAY()).

2. Respect filters on your Calendar (so that MAX(Calendar[Date]) automatically honors the current DBName and currency‐code filters) and also handle periods with no transactions.

3. Fetch the “last available” FX rate from your rate table (PP_Currency_rate), finding the single rate record where:

   • name_2 = current currency code, and

   • date ≤ LookupDate,

   • then picking the newest of those dates (TOPN(1, … date DESC) or equivalent).

4. Only display a rate on rows where a currency code is in context (i.e. at the currency or transaction level), and return blank everywhere else (e.g. on pooled DBName totals).

---

Desired Outcome

• FX Rate Daily shows, for each matrix cell, the FX rate on exactly that row’s date (transaction date or calendar day, or period‐end when collapsed).

• FX Rate MonthEnd always shows the rate at the end of the visible period (e.g. month‐end even at the Day level).

• FX Rate SelectedEndDate always shows the rate on the single date chosen by your Calendar slicer (clamped to today), ignoring the drill hierarchy.

This logic must yield exactly the results illustrated in your Excel mockup for every combination of drill level and slicer setting.

Data model & requirements

• Transactions table: I03_DKD

  • Transaction date: I03_DKD[I02_OP_DATA.1]

  • Currency code: I03_DKD[N01_ACCT.N01_KODAS_VL]

• Calendar table: Calendar[Date] with hierarchy Year → Month → Day.

• FX rates table: PP_Currency_rate

  • Currency code: PP_Currency_rate[name_2]

  • Rate date: PP_Currency_rate[date]

  • Rate value: PP_Currency_rate[amount_2]

• Depth slicer: CurrencyDepth[Depth] with values

  • "Daily"

  • "MonthEnd"

  • "SelectedEndDate"

I need to build three separate measures:

1. FX Rate Daily

2. FX Rate MonthEnd

3. FX Rate SelectedEndDate

Each measure must:

1. Determine a lookup date (LookupDate) dynamically based on:

   • Matrix drill level:

     • If drilled into transactions → use the transaction date.

     • If at Day level → use that day.

     • If at Month level → use the last day of that month.

     • If at Year level → use the last day of that year (but never a future date).

   • Depth slicer selection:

     • For Daily, lookup = the date above.

     • For MonthEnd, lookup = month-end of that date.

     • For SelectedEndDate, lookup = the date chosen in the calendar slicer (clamped to ≤ TODAY()).

2. Respect all existing filters:

   • The Calendar → I03_DKD relationship is single-directional, so MAX(Calendar[Date]) isn’t picking up filters on DBName or currency code by default. I need a pattern that honors both the drill-through filters and any slicers without switching my model to bidirectional.

3. Fetch the last available FX rate:

   • Find the record in PP_Currency_rate where

     name_2 = current currency code  
       && date ≤ LookupDate  

   • Return the rate for the latest date (using TOPN(1, … date DESC) or equivalent).

4. Show the measure only at currency/transaction levels; return blank at summary levels (DBName or higher).

Expected behavior (based on my Excel mock-up)

Matrix drilled to Day level (e.g. 2025-01-05)

Row FX Rate Daily FX Rate MonthEnd FX Rate SelectedEndDate

USD (header)	rate @01-05	rate @01-31	rate @slicer date
2020-10-29	rate @10-29	rate @10-31	rate @slicer date
2019-08-07	rate @08-07	rate @08-31	rate @slicer date

Matrix collapsed to Month level (January 2025)

Row FX Rate Daily FX Rate MonthEnd FX Rate SelectedEndDate

USD (header)	rate @01-31	rate @01-31	rate @slicer date
2020-10-29	rate @10-29	rate @10-31	rate @slicer date
…	…	…	…

Matrix collapsed to Year level (2025)

Row FX Rate Daily FX Rate MonthEnd FX Rate SelectedEndDate

USD (header)	rate @12-31	rate @12-31	rate @slicer date
2020-10-29	rate @10-29	rate @10-31	rate @slicer date
…	…	…	…

What I’ve tried

• SWITCH(TRUE(), ISINSCOPE(...), …) patterns combined with TOPN + MAXX.

• Using CROSSFILTER inside measures to force Calendar filtering.

• LOOKUPVALUE for exact dates, but that fails when there’s no rate record on a given day.

• Multiple nested IF/BLANK() checks, which end up returning blanks or mis-clamped dates.

None of these have given me a single, compact pattern that:

• Always chooses the correct LookupDate across all drill levels and slicer settings.

• Honors my existing single-direction relationships.

• Executes quickly on a 40 million-row model.

---

Request for help

Could you please share your best-practice DAX patterns or complete measure definitions for these three scenarios, ensuring:

• Minimal branching (no excessive IF/BLANK() logic).

• Proper filter propagation on Calendar[Date] without changing model relationships.

• Efficient rate lookup (TOPN + date filter or CALCULATE(MAX, …)).

• Scalability to large datasets (40 M+ rows).

Any code snippets or performance tips would be greatly appreciated—thank you in advance!

PBIX test attached https://limewire.com/d/o6cTZ#olY43xItQN

[v-veshwara-msft]

Hi @Digger ,
Thanks for posting in Microsoft Fabric Community.

Could you please confirm if the response from @tharunkumarRTK has been helpful in resolving your query. If you need any further assistnace, please reach out.

Thanks to @tharunkumarRTK for looking into this and providing valuable insights.

Thank you.

[Digger]

@v-veshwara-msft of course it's not helpful at all as it doesn't solve the problem, only spams with posts. 😠 #Problem #Spam #Posts

[tharunkumarRTK]

@Digger 

I looked into your file, my observations

1. I guess to reduce the file size you removed a lot of dat from your 'I03_DKD' table, it does not contain 2025 data and for 2024, it has jan feb and march data only. 

2. Also, 2024 records corresponds to currency code EUR but your currency table does not contain this code.

its hard for anyone to guess which year's data is in right shape so that they can write the formula.

3. There is one many to many relationship, as you are concerned about performance you should consider handling this 

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

4. I am not sure why you are using actual date column from fact table in rows and date column from the calendar table into columns section of matrix, these two tables are related and those two are the actual date columns on top of which you created the relationship, I guess you need to remove them from columns section

I looked into your measure and you splitted the logic properly into variables, I would sugget you to fix the issue then debug the logic varaible by variable, 

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[FBergamaschi]

Is in your pbix any measure you already started creating as a pre-work for the three you are asking to fix? So we can start from that point?

Anyway I would start checking this to get inspiration

https://www.daxpatterns.com/currency-conversion/

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Digger]

@FBergamaschi It feels like you're more focused on posting than actually checking the file. Would appreciate it if you could review it before commenting.

[Digger]

@jaineshp  chatgpt to stupid to solve this!!!

[v-sdhruv]

Hi @Digger ,

Just wanted to check if you were able to resolve the issue.

If you’ve made any progress or have a working pattern you'd like to share, I’d love to see it—it could help us all learn from your approach.

Thanks again for your patience and collaboration!

[v-sdhruv]

Hi @Digger ,

We understand your concern and acknowledge that this limitation can be frustrating. However, we’ve shared a possible workaround based on the information currently available to us. If there’s any way you’re able to make it work despite the constraint, we’d greatly appreciate it if you could share your approach with the community—it could be incredibly helpful to others facing similar challenges.

Please let us know if you need any further details or support.

Best regards,
Shruti

[v-sdhruv]

Hi @Digger ,

The LookupDate  measure is designed to dynamically determine the correct date to use for FX rate lookups based on two factors: Depth slicer selection  and Drill level in the matrix 

It uses ISINSCOPE() to detect the current drill level and selects the appropriate date: At Transaction level,Day level,Month level and  Year level.

Beyond this, there’s no reliable way to solve the problem more generically within DAX. Power BI doesn’t expose drill level context beyond what ISINSCOPE() can detect, and it doesn’t allow for dynamic evaluation of slicer selections across unrelated hierarchies in a more granular way. So this approach is about as flexible and robust as DAX allows for this scenario.

Hope this helps clarify things!

Best regards,
Shruti

[Digger]

@v-sdhruv you wrong, it is possible!!

[v-sdhruv]

Hi @Digger ,

Thank you for such detailed explaination about your requirement and sharing the pbix.

I tried re-producing the scenario and here are the DAX measures that you can try-

For LookupDate

LookupDate = 
VAR SelectedDepth = SELECTEDVALUE(CurrencyDepth[Depth])
VAR CurrentDate = MAX('__Calendar'[Date])  -- assumes 'Date' table is used in matrix
VAR DrillLevel =
    SWITCH(
        TRUE(),
        ISINSCOPE('__Calendar'[Date].[Day]), "Day",
        ISINSCOPE('__Calendar'[Date].[Month]), "Month",
        ISINSCOPE(__Calendar[Date].[Year]), "Year",
        "Transaction"
    )
VAR Result =
    SWITCH(
        TRUE(),
        SelectedDepth = "Daily" && DrillLevel = "Transaction", MAX(I03_DKD[I02_OP_DATA.1]),
        SelectedDepth = "Daily" && DrillLevel = "Day", CurrentDate,
        SelectedDepth = "Daily" && DrillLevel = "Month", EOMONTH(CurrentDate, 0),
        SelectedDepth = "Daily" && DrillLevel = "Year", MIN(EOMONTH(DATE(YEAR(CurrentDate), 12, 31), 0), TODAY()),

        SelectedDepth = "MonthEnd", EOMONTH(CurrentDate, 0),
        SelectedDepth = "SelectedEndDate", MIN(EOMONTH(MAX('__Calendar'[Date]), 0), Today())
    )
RETURN Result

For FX Rate-

Get FX Rate = 
VAR LookupDate = [LookupDate]
VAr CurrCode = max(PP_Currency_rate[name_2])
RETURN
IF(
    NOT ISBLANK(LookupDate) && NOT ISBLANK(CurrCode),
    CALCULATE(
        MAX(PP_Currency_rate[amount_2]),
        FILTER(
            PP_Currency_rate,
            PP_Currency_rate[name_2]<>BLANK() &&
            PP_Currency_rate[date] <= LookupDate
        )
    )

)

 Then you can use 3 explicit measures shared earlier by the Superuser  for Daily, MonthEnd and SelectedEndDate.
This will give you the results.

Attached file for reference.
Also, Thank you @AmiraBedh for sharing your inputs.
Hope this helps!

[Digger]

@v-sdhruv but it totaly wrong, should be different rates for different currencies, also total should be blank

also i dont have such scopes

        ISINSCOPE('__Calendar'[Date].[Day]), "Day",
        ISINSCOPE('__Calendar'[Date].[Month]), "Month",
        ISINSCOPE(__Calendar[Date].[Year]), "Year",

only rows scopes should be

ISINSCOPE( I03_DKD[I02_OP_DATA.1] )

ISINSCOPE('N01_ACCT'[N01_KODAS_VL])

[AmiraBedh]

Hello !

Thank you for posting on Fabric community.

Well I tried to follow you logic and I can tell you thay you may not rely on Power BI only to achieve what you want.

Why ?

Simply because Power BI can struggle when:

• your lookup logic depends on dynamic joins with time-based filtering per row.

• you have large row-level grain that doesn’t cache well (40M rows with 3 complex measures = 120M lookups)

• you need repeated access to expensive logic (like MAX FX rate <= date)

What I managed to understand (I did try it on small amount of data), you can create a measure for the dynamic LookupDate to avoid duplicating logic across measures:

Selected Lookup Date = 
VAR Depth = SELECTEDVALUE(CurrencyDepth[Depth])
VAR TodayDate = TODAY()

VAR DrillDate =
    SWITCH(
        TRUE(),
        ISINSCOPE(I03_DKD[I02_OP_DATA.1]), MAX(I03_DKD[I02_OP_DATA.1]),
        ISINSCOPE('Calendar'[Date]), MAX('Calendar'[Date]),
        ISINSCOPE('Calendar'[Month]), EOMONTH(MAX('Calendar'[Date]), 0),
        ISINSCOPE('Calendar'[Year]), MIN(EOMONTH(MAX('Calendar'[Date]), 0), TodayDate),
        BLANK()
    )

VAR MonthEndDate =
    IF(NOT ISBLANK(DrillDate), EOMONTH(DrillDate, 0))

VAR SelectedDate =
    CALCULATE(
        MAX('Calendar'[Date]),
        REMOVEFILTERS('Calendar')
    )

VAR ClampedSelectedDate = 
    IF(SelectedDate > TodayDate, TodayDate, SelectedDate)

RETURN
    SWITCH(
        Depth,
        "Daily", DrillDate,
        "MonthEnd", MonthEndDate,
        "SelectedEndDate", ClampedSelectedDate,
        BLANK()
    )

and you need a reusable pattern to get the latest available FX rate up to the LookupDate, to return the most recent FX rate up to the lookup date for the current currency in context :

Get FX Rate =
VAR LookupDate = [Selected Lookup Date]
VAR CurrCode = SELECTEDVALUE(I03_DKD[N01_ACCT.N01_KODAS_VL])

RETURN
IF(
    NOT ISBLANK(LookupDate) && NOT ISBLANK(CurrCode),
    CALCULATE(
        MAX(PP_Currency_rate[amount_2]),
        FILTER(
            PP_Currency_rate,
            PP_Currency_rate[name_2] = CurrCode &&
            PP_Currency_rate[date] <= LookupDate
        )
    )
)

Then you can define 3 explicit measures :

FX Rate Daily = 
VAR __Depth = SELECTEDVALUE(CurrencyDepth[Depth])
RETURN
IF(__Depth = "Daily", [Get FX Rate])

FX Rate MonthEnd = 
VAR __Depth = SELECTEDVALUE(CurrencyDepth[Depth])
RETURN
IF(__Depth = "MonthEnd", [Get FX Rate])

FX Rate SelectedEndDate = 
VAR __Depth = SELECTEDVALUE(CurrencyDepth[Depth])
RETURN
IF(__Depth = "SelectedEndDate", [Get FX Rate])

What I really want you to understand in your case you may rethink of stepping back and ask if some of this complexity belongs outside of Power BI.

[Digger]

I know how to use ChatGPT, and nothing it outputs works!!! That’s why I’m asking for help here, as I’ve exhausted all options. If you had inserted these formulas into my PBIX file, you would see that they don’t work.

[AmiraBedh]

I didn't work directly on the pbix file you have I just tried to work on some data after following the logic you shared.

I just used Chatgpt to reformulate my answer. I will try to take time on the example you shared, but I really want you to take my advice about (since I worked on something similar in the past):

What I really want you to understand in your case you may rethink of stepping back and ask if some of this complexity belongs outside of Power BI.

Thank you for your understanding.