Calculated Column, 2 sets of calculations based on ID# from another table

[jasmin_w]

I am trying to create a calculated column, but I need to do 2 different calculations based on ID number. I have a table of the Exempt ID numbers, a table with the Employee data, and a table that contains the actual data values. 

Table Names

Exempt Employees - table that includes ID numbers that I want to do a different calculation for.

Employee Data - table that contains employee information and connects all the data sources

Main - table that includes the data values by employee number.

Pretty much, I want to have 2 sets of calculations based on if their ID number is in the Exempt Employee Table.

• If ID in Exempt Employee then 
  • 'Main'[Base Salary] * 'Main'[Multiplier]  / 4
• If ID not in Exempt Employees then 
  • ('Main'[Base Salary] * 'Main'[Multiplier]) * 0.8 )) / 4

What is the best way to do this? I can also change the relationships between the 3 tables if there is a solution that way too.

[Ashish_Mathur]

Hi,

In which of those 3 tables do you want to write those formulas?  Share the download link of the PBI file.

[v-nuoc-msft · Accepted solution]

Hi @jasmin_w 

For your question, here is the method I provided:

Here's some dummy data

“Exempt Employees”

“Employee”

“Main”

Relationships between tables.

Create a Calculated Column. This function queries whether the ID returned from the Exempt Employees table is a relevant value indicating that the employee is exempt.

Salary Calculation = 
    var _isExempt = 
        NOT(
            ISBLANK(
                RELATED('Exempt Employees'[ID])
            )
        )
RETURN 
    IF(
        _isExempt,
        ('Main'[Base Salary] * 'Main'[Multiplier] * 0.8) / 4, 
        ('Main'[Base Salary] * 'Main'[Multiplier] ) / 4
    )

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jasmin_w]

Thank you so much. This worked perfectly!