CODEWITHSUNDEEP
daxpowerpivot
user6241
user6241

Reputation: 101

How do I use Calculate() in Powerpivot as an alternative to if function

My question is this. I have created an IF formula in Excel - which works fine. However, I got an error message when I tried to use this formula as a calculated column in PowerPivot. Apparently because of the number of ors in it. So basically I need to create it as a measure. I reckon I need a CALCULATE() function but just want a couple of pointers on where to start. I know I have to reference the table name in it not just the column name. 

IF(OR([Deferred %]=1,[Deferred %]=0, [In Contract Date]="NO"),
    0,
    IF([S&M  Contract Start date]=[Revenue Date],
        ([Amount]*(1-[Deferred %])+[Amount]/[Revenue Days]),
        ([Deferred %]*[Amount])/[Revenue Days]),0)

Thanks.

Upvotes: 0

Views: 65

Answers (1)

Marcus
Marcus

Reputation: 541

This would be because in power pivot calculation language DAX the OR and AND functions only take two inputs, unlike excel which allows for multiple inputs.

A simple way around this would be the following:

OR(OR([Deferred %]=1,[Deferred %]=0), [In Contract Date]="NO")

The other option is using || which is for or. This replacing your condition as,

[Deferred %]=1 || [Deferred %]=0 || [In Contract Date]="NO"

Upvotes: 1

Related Questions