How to add an if statement in a DAX formula measure?

0
votes

I am new to DAX and have created two measures to get the total pay per employee then lookup that total pay into a table and return a value in my Power Pivot.

Total Pay Measure:

NMRPaySum:=SUMX(Pay,[Regular Pay]+[Overtime Pay]+[Other Pay])

Range Lookup Measure:

SSSContributionEE :=
CALCULATE (
    VALUES ( SSSContribution[EE] ),
    FILTER (
        SSSContribution,
        [NMRPaySum] >= SSSContribution[Lower Bound] &&
        [NMRPaySum] <= SSSContribution[Upper Bound]
    )
)

However, I need the range lookup to only calculate if the employee type is satisfied.

The logic for it is below:

If Employee[Type]="Regular" Then
    Calculate SSSConbtributionEE
Else
    0
End If

I have tried this DAX formula, but doesn't seem to be working:

=
IF (
    OR ( Salary[Type] = "Regular", Salary[Type] = "Agency" ),
    CALCULATE (
        VALUES ( SSSContribution[EE] ),
        FILTER (
            SSSContribution,
            [NMRPaySum] >= SSSContribution[Lower Bound] &&
            [NMRPaySum] <= SSSContribution[Upper Bound]
        )
    ),
    0
)

NMRPay Table: enter image description here

SSS Contribution Table:

enter image description here

Employee Information Table:

enter image description here

2
excelpowerbidaxpowerpivot

2 Answers

0
votes

Use your measure as is, for all the data. You then build a Pivot table, where you can use a filter or slicers on Employee Type to exclude unwanted values. Add the measure to the Values area and it will only calculate for data that is in the rows and columns of the pivot table.

0
votes

For the OR condition, you should be able to add that as another filter:

=  CALCULATE (
        VALUES ( SSSContribution[EE] ),
        FILTER (
            SSSContribution,
            [NMRPaySum] >= SSSContribution[Lower Bound] &&
            [NMRPaySum] <= SSSContribution[Upper Bound]
        ),
        FILTER (Salary, Salary[Type] = "Regular" || SalaryType = "Agency")
)

This may or may not work depending on your exact data model / relationship structure, but it might point you in the right direction. It's possible you need to use RELATED / RELATEDTABLE, but I'm not sure without being able to play with it myself.