Forum Discussion

mgavin's avatar
mgavin
New Member
1 year ago
Solved

Measure based on value at selected time.

Hi,   I am pretty new to Power BI and tasked with creating an employee dashboard to track current annual goal status. These goals are dependant on position and are converted to a score of 3, 2, or ...
  • HiteshDataXpert's avatar
    HiteshDataXpert
    1 year ago

    Create a Calculated Column to Determine the Correct Position

    In the scores table, create a calculated column to determine the employee's position at the time of the score. This column will use the Effective Date to find the correct position.

    DAX
     
    PositionAtDate = 
VAR CurrentDate = 'Scores'[Date]
VAR CurrentEmployee = 'Scores'[Employee]
RETURN
    CALCULATE(
        MAX('Employees'[Position]),
        FILTER(
            'Employees',
            'Employees'[Name] = CurrentEmployee &&
            'Employees'[Effective Date] <= CurrentDate
        )
    )

    Update the Score Calculation

    Modify your SCORE_1 measure to use the PositionAtDate column instead of the static Position column.

    DAX
    SCORE_1 = 
VAR CurrentPosition = SELECTEDVALUE('Scores'[PositionAtDate])
VAR CurrentAverage = SELECTEDVALUE('Scores'[Average 1])
RETURN
    SWITCH(
        TRUE(),
        CurrentPosition = "Position 1" && CurrentAverage >= 0.985, 3,
        CurrentPosition = "Position 1" && CurrentAverage >= 0.98, 2,
        CurrentPosition = "Position 1", 1,
        CurrentPosition = "Position 2" && CurrentAverage >= 0.99, 3,
        CurrentPosition = "Position 2" && CurrentAverage >= 0.985, 2,
        CurrentPosition = "Position 2", 1,
        CurrentPosition = "Position 3" && CurrentAverage >= 0.995, 3,
        CurrentPosition = "Position 3" && CurrentAverage >= 0.99, 2,
        CurrentPosition = "Position 3", 1,
        0
    )
HiteshDataXpert's avatar
HiteshDataXpert
New Member
1 year ago

Create a Calculated Column to Determine the Correct Position

In the scores table, create a calculated column to determine the employee's position at the time of the score. This column will use the Effective Date to find the correct position.

DAX
 
PositionAtDate = 
VAR CurrentDate = 'Scores'[Date]
VAR CurrentEmployee = 'Scores'[Employee]
RETURN
    CALCULATE(
        MAX('Employees'[Position]),
        FILTER(
            'Employees',
            'Employees'[Name] = CurrentEmployee &&
            'Employees'[Effective Date] <= CurrentDate
        )
    )

Update the Score Calculation

Modify your SCORE_1 measure to use the PositionAtDate column instead of the static Position column.

DAX
SCORE_1 = 
VAR CurrentPosition = SELECTEDVALUE('Scores'[PositionAtDate])
VAR CurrentAverage = SELECTEDVALUE('Scores'[Average 1])
RETURN
    SWITCH(
        TRUE(),
        CurrentPosition = "Position 1" && CurrentAverage >= 0.985, 3,
        CurrentPosition = "Position 1" && CurrentAverage >= 0.98, 2,
        CurrentPosition = "Position 1", 1,
        CurrentPosition = "Position 2" && CurrentAverage >= 0.99, 3,
        CurrentPosition = "Position 2" && CurrentAverage >= 0.985, 2,
        CurrentPosition = "Position 2", 1,
        CurrentPosition = "Position 3" && CurrentAverage >= 0.995, 3,
        CurrentPosition = "Position 3" && CurrentAverage >= 0.99, 2,
        CurrentPosition = "Position 3", 1,
        0
    )