cancel
Showing results for
Did you mean:
Highlighted
New Member

## IFS in DAX

Hello everybody,

I have these data:

 ACT/BUD Amount % BUD 10 1 ACT 5 0,461538462 DELTA -5 -0,53846154 BUD 3 1 ACT 1 0,461538462 DELTA -2 -0,53846154

% is actually, what I need to calculate.

I used following formula:

'02 Deníky vše' is the table

% = SUMX (
'02 Deníky vše';
IF (
'02 Deníky vše'[BUD/ACT] = "ACT";CALCULATE(SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';"ACT")/SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';"BUD"));

IF ( '02 Deníky vše'[BUD/ACT] = "BUD";1;(CALCULATE(SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';"ACT")/SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';"BUD")))-1 )
)
)

I get the following error message:
A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

The logic is that:
%BUD(Budget) is always 1 (100%)
%ACT (actual) is ACT/BUD
%DELTA is %ACT-%BUD

Thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: IFS in DAX

For multiple IFs, I would recommend you use a SWITCH function:

``````%_ =
VAR BUD_= 1
VAR ACT_ = SUMX(FILTER('02 Deníky vše',[BUD/ACT]="ACT"),[Amount])/SUMX(FILTER('02 Deníky vše','02 Deníky vše'[BUD/ACT]="BUD"),[Amount])
VAR DELTA_ = ACT_- BUD_

RETURN  SWITCH([BUD/ACT], "BUD",BUD_, "ACT",ACT_,"DELTA",DELTA_)``````

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

4 REPLIES 4
Highlighted
Super User IV

## Re: IFS in DAX

The column name is missing in the filter

`````` SUMX (
'02 Deníky vše';
IF (
'02 Deníky vše'[BUD/ACT] = "ACT";CALCULATE(SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';'02 Deníky vše'[ACT/BUD]="ACT")/SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';'02 Deníky vše'[ACT/BUD]="BUD"));

IF ( '02 Deníky vše'[BUD/ACT] = "BUD";1;(CALCULATE(SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';'02 Deníky vše'[BUD/ACT]="ACT")/SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';'02 Deníky vše'[BUD/ACT]="BUD")))-1 )
)
)``````

Proud to be a Super User!

Highlighted
New Member

## Re: IFS in DAX

Hi amitchandak,

thanks for the tip! Unfortunately, I still get the "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed." error message and it won't work.

Highlighted
Super User IV

## Re: IFS in DAX

I did a few more corrections. Format it better to find the error

`````` SUMX (
'02 Deníky vše';
IF (
'02 Deníky vše'[BUD/ACT] = "ACT";(CALCULATE(SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';'02 Deníky vše'[ACT/BUD]="ACT"))/SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';'02 Deníky vše'[ACT/BUD]="BUD"));

IF ( '02 Deníky vše'[BUD/ACT] = "BUD";1;(CALCULATE(SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';'02 Deníky vše'[BUD/ACT]="ACT"))/SUM('02 Deníky vše'[Amount]);FILTER('02 Deníky vše';'02 Deníky vše'[BUD/ACT]="BUD"));blank()))
-1
)
``````

Proud to be a Super User!

Highlighted
Community Support

## Re: IFS in DAX

For multiple IFs, I would recommend you use a SWITCH function:

``````%_ =
VAR BUD_= 1
VAR ACT_ = SUMX(FILTER('02 Deníky vše',[BUD/ACT]="ACT"),[Amount])/SUMX(FILTER('02 Deníky vše','02 Deníky vše'[BUD/ACT]="BUD"),[Amount])
VAR DELTA_ = ACT_- BUD_

RETURN  SWITCH([BUD/ACT], "BUD",BUD_, "ACT",ACT_,"DELTA",DELTA_)``````

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021