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

[Digger]

Hello everyone,

[Digger]

Hello everyone,

[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.

[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!

[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.