Forum Discussion

DianaDM96's avatar
DianaDM96
Frequent Visitor
9 months ago
Solved

TREATAS with Event Database

Hello community,     I am attaching a sample PBI report via WeTransfer. I have an Employee Events table, where each employee transaction has an Effective Start Date and Effective End Date. I have ...
DAX
TREATAS
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    9 months ago

    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)

     

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    9 months ago

    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 ☺️❤️

Ahmed-Elfeel's avatar
Ahmed-Elfeel
Super User
9 months ago

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)

 

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
DianaDM96's avatar
DianaDM96
Frequent Visitor
9 months ago

Hello Ahmed-Elfeel, thank you very much! This is fantastic, it does exactly what I need. My days are not very good, so I was actually stressed because of this formula, but now it works 🙂 You`re amazing!

 

I only got one question if I may. How can I make the total show the SUM of all the divided values? So instead of 946.63, it does 2191.78 + 535.91 -> in both grand total of table and KPI card

 

I tried several things already: 

Annualized Salary (USD) = 
VAR OriginalFormula = SUMX('Employee Events', DIVIDE([Annualized Salary (LC)], [FX Value 3]))
VAR SelectedDate = MAX('Calendar'[Date])
VAR LocalSalary = [Annualized Salary (LC)]
VAR FXRate = [FX Value 3]
VAR ConvertedSalary = DIVIDE(LocalSalary, FXRate, 0)
VAR SalaryTable = ADDCOLUMNS( 'Calendar', "ConvertedSalary", ConvertedSalary)
VAR TotalConvertedSalary = SUMX( SalaryTable, ConvertedSalary )
VAR HasOneValueFormula = IF( HASONEVALUE( 'Calendar'[Date] ), ConvertedSalary, TotalConvertedSalary)

RETURN
HasOneValueFormula

 

Annualized Salary (USD) = 
VAR OriginalFormula = SUMX('Employee Events', DIVIDE([Annualized Salary (LC)], [FX Value 3]))
VAR SelectedDate = MAX('Calendar'[Date])
VAR LocalSalary = [Annualized Salary (LC)]
VAR FXRate = [FX Value 3]
VAR ConvertedSalary = DIVIDE(LocalSalary, FXRate, 0)
VAR SalaryTable = ADDCOLUMNS( 'Calendar', "ConvertedSalary", ConvertedSalary)
VAR TotalConvertedSalary = SUMX( SalaryTable, ConvertedSalary )
VAR HasOneValueFormula = IF( HASONEVALUE( 'Calendar'[Date] ), ConvertedSalary, TotalConvertedSalary)

RETURN
CALCULATE (
    SUMX ( 'Employee Events', ConvertedSalary ),
    'Employee Events'[Effective Start Date] <= MAX ( 'Calendar'[Date] )
        && 'Employee Events'[Effective End Date] >= MAX ( 'Calendar'[Date] ),
    REMOVEFILTERS ( 'Calendar' )
)

 

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    9 months ago

    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 ☺️❤️