Forum Discussion

newhopepdx's avatar
newhopepdx
Resolver I
2 years ago
Solved

Filtered Table

tblSalaryModifications records list all changes to an employee's salary or hourly rate. I'm trying to create a measure that will yield the annual Wage for the (slicer) selected emplolyee, by (additionally) filtering the table down to the most recent salary change.
The measure below errors out saying that the table in the VAR curEmp_ is "not available". Where have I gone wrong?
 
Est Annual Wages =
VAR lastedate_ = MAX( 'tblSalaryModifications'[DtAssigned] )
VAR curEmp_ = FILTER( 'tblSalaryModifications', 'tblSalaryModifications'[DtAssigned] = lastedate_ )
VAR estAnnualWage_ =
    IF( curEmp_[Salary] > 0,
    curEmp_[Salary],
    curEmp_[HoursPerWeek] * curEmp_[HourlyRate] * 52
    )
    RETURN estAnnualWage_
Need Help
  • newhopepdx's avatar
    newhopepdx
    2 years ago

    Your code gave me the prodding I needed to work out the solution.

    Here's the code that actually works:

    Est Annual Wages =
    VAR _lastChangetbl = FILTER(
            ALLSELECTED('tblSalaryModifications'),
            'tblSalaryModifications'[DtAssigned] = MAX('tblSalaryModifications'[DtAssigned] ))
    VAR _salary = SUMX( _lastChangetbl, [Salary])
    VAR _annualHourly = SUMX( _lastChangetbl, [HourlyRate] * [HoursPerWeek] ) * 52
    RETURN
        IF(
            _salary > 0,
            _salary,
            _annualHourly
        )

2 Replies

Replies have been turned off for this discussion
  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    newhopepdx , Follow the code of lastest given here

     

    Last Qty = Var _max = maxx(filter( ALLSELECTED('tblSalaryModifications'), 'tblSalaryModifications'[EMPID] = max('tblSalaryModifications'[EMPID] )), 'tblSalaryModifications'[DtAssigned])
    return
    CALCULATE(sumx('tblSalaryModifications',[HoursPerWeek] *[HourlyRate] * 52 ), filter( ('tblSalaryModifications'), 'tblSalaryModifications'[EMPID] = max('tblSalaryModifications'[EMPID]) && 'tblSalaryModifications'[Date] =_max))


    Sum Last Qty = sumx(VALUES('tblSalaryModifications'[EMPID]) , [Last Qty])

     

     

     

    Latest
    https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

    https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e

    • newhopepdx's avatar
      newhopepdx
      Resolver I
      2 years ago

      Your code gave me the prodding I needed to work out the solution.

      Here's the code that actually works:

      Est Annual Wages =
      VAR _lastChangetbl = FILTER(
              ALLSELECTED('tblSalaryModifications'),
              'tblSalaryModifications'[DtAssigned] = MAX('tblSalaryModifications'[DtAssigned] ))
      VAR _salary = SUMX( _lastChangetbl, [Salary])
      VAR _annualHourly = SUMX( _lastChangetbl, [HourlyRate] * [HoursPerWeek] ) * 52
      RETURN
          IF(
              _salary > 0,
              _salary,
              _annualHourly
          )