cancel
Showing results for
Did you mean:
Frequent Visitor

## 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
Community Support

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
Community Support

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.

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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Frequent Visitor

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.

Super User

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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Announcements

#### Power BI May 2023 Update

Find out more about the May 2023 update.

#### Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors