Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 

    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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

        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 
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

    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.