IFS in DAX

[Vilem]

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

Could anyone help, please?

Thanks!!

[V-pazhen-msft · Accepted solution]

@Vilem 

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.

[amitchandak]

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 )
)
)

[Vilem]

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.

[amitchandak]

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 
)