Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Vilem
Frequent Visitor

IFS in DAX

Hello everybody,

 

I have these data:

ACT/BUDAmount%
BUD101
ACT50,461538462
DELTA-5-0,53846154
BUD31
ACT10,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!!

 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

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

 

 

switch AUC BUD.JPG

 

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

View solution in original post

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

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

 

 

switch AUC BUD.JPG

 

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

amitchandak
Super User
Super User

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

 

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.

 

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 
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.