TREATAS with Event Database

Hi DianaDM96,

I hope you are doing well today ☺️❤️

So your issue is that your FX Value 3 measure is using SUMMARIZE over Employee Events which creates a dependency on the current filter context from your date slicer (When no employee events match the selected date the virtual relationship fails)

So How to Solve this issue?

Here is a better approach using TREATAS you can Try:

FX Value 3 = 
VAR SelectedDate = MAX('Calendar'[Date])
VAR EndOfMonthDate = EOMONTH(SelectedDate, 0)
VAR CurrenciesInScope =
    CALCULATETABLE(
        VALUES('Employee Events'[Currency]),
        'Employee Events'[Effective Start Date] <= SelectedDate,
        'Employee Events'[Effective End Date] >= SelectedDate,
        REMOVEFILTERS('Calendar')
    )
RETURN
CALCULATE(
    MAX('FX Rates'[Value]),
    TREATAS(
        CurrenciesInScope,
        'FX Rates'[Currency]
    ),
    'FX Rates'[Date] = EndOfMonthDate,
    REMOVEFILTERS('Calendar')
)

And for your Annualized Salary in USD measure:

Annualized Salary (USD) = 
VAR SelectedDate = MAX('Calendar'[Date])
VAR BaseSalary = [Annualized Salary (LC)]
VAR FXRate = [FX Value 3]
RETURN
DIVIDE(BaseSalary, FXRate, 0)

This should give you the expected outcome where selecting any date in the Date slicer will show Annualized Salary (USD) and FX Value for all users who are active at that point in time (not just those with events starting on the selected date)

Hi DianaDM96,

Your welcome! I am glad I could help reduce your stress that's what we are here for! 😊

The problem is that your measure is calculating at the row level correctly but at the total level, it is not iterating through each employee individually to convert their salary

So You could try this and i hope it work:

Annualized Salary (USD) = 
VAR SelectedDate = MAX('Calendar'[Date])
VAR ActiveEmployees = 
    CALCULATETABLE(
        'Employee Events',
        'Employee Events'[Effective Start Date] <= SelectedDate,
        'Employee Events'[Effective End Date] >= SelectedDate,
        REMOVEFILTERS('Calendar')
    )
RETURN
SUMX(
    ActiveEmployees,
    VAR EmployeeSalary = 'Employee Events'[Annualized Salary (LC)]
    VAR EmployeeCurrency = 'Employee Events'[Currency]
    VAR FXRate = 
        CALCULATE(
            MAX('FX Rates'[Value]),
            TREATAS({EmployeeCurrency}, 'FX Rates'[Currency]),
            'FX Rates'[Date] = EOMONTH(SelectedDate, 0),
            REMOVEFILTERS('Calendar')
        )
    RETURN
    DIVIDE(EmployeeSalary, FXRate, 0)
)

Tell me if It works ☺️❤️