# Use If() function in powerpivot measures

• ### Question

• Hi together,

I have a measure to determine the value  "2 " from different columns (see below). It works well. In the next step, I want to return a value of  "1 " If the result of Measure_awa is > 4.

measure_awa:=

calculate(count([awa60]);bewertung[awa60]=2)
+
calculate(count([awa61]);bewertung[awa61]=2)
+
calculate(count([awa62]);bewertung[awa62]=2)
+
calculate(count([awa63]);bewertung[awa63]=2)

Thanks

Guenter

• Changed type Wednesday, February 7, 2018 8:13 AM More a question then a discussion
Wednesday, February 7, 2018 7:56 AM

• thanks for your support. The goal is this: I want to read from several columns of a table the value 2, which stands for a certain state. If I find the value 2 more than twice in multiple columns, it means that a limit has been exceeded. In this case I need a value of 1 for further evaluation / calculation. Otherwise, if the number is not occurs more than twice, the result of the measure can also be blank.

I think PowerPivot has a logical query Problem with IF in measures, such as if () to act with the if () function.

I don't think there is any problem with the IF function. I think it's just that we have not had a clear understanding of your requirements. The previous attempts are all working at the aggregate level, they count all the rows that have a value of 2, then add them together, then check if that amount is > 2. Whereas now it sounds like you want to check within each row, if there are more than 2 columns with a value of 2.

To evaluate row by row you would use an iterator function (which all end with "X") like SUMX or COUNTX

The following measure will give you a count of all rows where more than 2 of the columns have a value of 2

SUMX( bewertung,
IF( IF( bewertung[awa60] = 2, 1) +
IF( bewertung[awa61] = 2, 1) +
IF( bewertung[awa62] = 2, 1) +
IF( bewertung[awa63] = 2, 1) ) > 2
, 1)

If this is still not correct can you maybe post a few rows of example data (it can be made up data, just to illustrate your issue) and the result you would expect to get from that sample data?

Thursday, February 8, 2018 8:21 PM
• I collect in the table evaluation test results. The exams include several examination questions per section. Exam questions are not relevant = 0, successful = 1, and error = 2. Each section can contain two errors. This means that if the note 2 appears more frequently than twice in the columns awa60 to awa63, the entire section has not been passed. So, if the score 2 is occcuring 3 or more the result has to be 1(the whole section has not been passed). This value is then further processed in other measures that evaluate other sections. Hui

Ok, so the expression I provided in my previous post should give you a count of all the responses that failed that section. Or you could take the inner IF expression and create a calculated column.

IF( IF( bewertung[awa60] = 2, 1) +
IF( bewertung[awa61] = 2, 1) +
IF( bewertung[awa62] = 2, 1) +
IF( bewertung[awa63] = 2, 1) ) > 2
, 1)

So that you could then use this column in further calculations

Friday, February 9, 2018 2:02 PM
• @Ousama - that would just be a localization feature, some countries use ; as a list separator and , as the decimal separator and the PowerPivot UI in Excel supports both styles. (we actually have a button in the tool bar for DAX Studio which will swap between the two styles to make it easier for people to use code samples that are in the other localization format)

@Guenter - I think I just missed including an opening "(" character in front of the inner IF statements. I created some dummy data and used the following formula and it seems to be working fine as far as I can tell

=IF( (IF( bewertung[awa60] = 2; 1) +
IF( bewertung[awa61] = 2; 1) +
IF( bewertung[awa62] = 2; 1) +
IF( bewertung[awa63] = 2; 1) ) > 2
; 1)

Friday, February 9, 2018 10:38 PM

### All replies

• Hi Guenter,

In this scenario, please try below DAX formula:

```=
IF (
CALCULATE ( COUNT ( [awa60] ), bewertung[awa60] = 2 )
+ CALCULATE ( COUNT ( [awa61] ), bewertung[awa61] = 2 )
+ CALCULATE ( COUNT ( [awa62] ), bewertung[awa62] = 2 )
+ CALCULATE ( COUNT ( [awa63] ), bewertung[awa63] = 2 )
> 4,
1,
CALCULATE ( COUNT ( [awa60] ), bewertung[awa60] = 2 )
+ CALCULATE ( COUNT ( [awa61] ), bewertung[awa61] = 2 )
+ CALCULATE ( COUNT ( [awa62] ), bewertung[awa62] = 2 )
+ CALCULATE ( COUNT ( [awa63] ), bewertung[awa63] = 2 )
)```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, February 7, 2018 8:42 AM
• Hi Willson,

it dosn't work.

The measure pulls the values from four columns and counts how often the value 2 is included.
If the value 2 is more common than 2x, I need a value of 1 as a result of the calculation.
I read in the forum that the true/false comparison in
measures is somewhat tricky, I cannot solve the problem at the moment.

Best Regards

Guenter

Wednesday, February 7, 2018 10:30 AM
• Hi together,

I have a measure to determine the value  "2 " from different columns (see below).
It works well. The measure pulls the values from four columns and counts how often the value 2 is included.

In the next step, I want to return a value of  "1 " if the result of Measure_awa is > 2.

If the value 2 is more common than 2x, I need a value of 1 as a result of the calculation.

But I read in the forum that the true/false comparison in measures is somewhat tricky, I cannot solve the problem at the moment.

measure_awa:=

calculate(count([awa60]);bewertung[awa60]=2)
+
calculate(count([awa61]);bewertung[awa61]=2)
+
calculate(count([awa62]);bewertung[awa62]=2)
+

Thanks

Guenter

Best Regards Guenter Prang

• Merged by Wednesday, February 7, 2018 4:45 PM Same question from same OP
Wednesday, February 7, 2018 3:22 PM
• Hi,

Create a new measure, and use the expression below :

new_measure:=IF(measure_awa>2,1,[The value you want if it is false])

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

[User Page]     [MSDN Page]     [Blog]     [Linkedin]

Wednesday, February 7, 2018 3:31 PM
• Hi Ousama,

I count with the measure_awa the number of digit two in multiple columns of a table. If the number 2 occurs more often than twice, I would like to evaluate this, preferably with a value of 1. But it doesn't work.

Crazy. But I have to work with a 32-bit powerpivot. Is that perhaps the reason?

Guenter

Best Regards Guenter Prang

Thursday, February 8, 2018 9:14 AM
• Hi,

No I don't think the 32-bit powerpivot is the problem.

Have you change the [The value you want if it is false] with the value you want :

new_measure:=IF(measure_awa>2,1,[The value you want if it is false])

If the number is not occurs more than twice, what is the value you want to get in this case?

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

[User Page]     [MSDN Page]     [Blog]     [Linkedin]

Thursday, February 8, 2018 9:22 AM
• Hi,

thanks for your support. The goal is this: I want to read from several columns of a table the value 2, which stands for a certain state. If I find the value 2 more than twice in multiple columns, it means that a limit has been exceeded. In this case I need a value of 1 for further evaluation / calculation. Otherwise, if the number is not occurs more than twice, the result of the measure can also be blank.

I think PowerPivot has a logical query Problem with IF in measures, such as if () to act with the if () function.

Guenter

Best Regards Guenter Prang

Thursday, February 8, 2018 12:31 PM
• thanks for your support. The goal is this: I want to read from several columns of a table the value 2, which stands for a certain state. If I find the value 2 more than twice in multiple columns, it means that a limit has been exceeded. In this case I need a value of 1 for further evaluation / calculation. Otherwise, if the number is not occurs more than twice, the result of the measure can also be blank.

I think PowerPivot has a logical query Problem with IF in measures, such as if () to act with the if () function.

I don't think there is any problem with the IF function. I think it's just that we have not had a clear understanding of your requirements. The previous attempts are all working at the aggregate level, they count all the rows that have a value of 2, then add them together, then check if that amount is > 2. Whereas now it sounds like you want to check within each row, if there are more than 2 columns with a value of 2.

To evaluate row by row you would use an iterator function (which all end with "X") like SUMX or COUNTX

The following measure will give you a count of all rows where more than 2 of the columns have a value of 2

SUMX( bewertung,
IF( IF( bewertung[awa60] = 2, 1) +
IF( bewertung[awa61] = 2, 1) +
IF( bewertung[awa62] = 2, 1) +
IF( bewertung[awa63] = 2, 1) ) > 2
, 1)

If this is still not correct can you maybe post a few rows of example data (it can be made up data, just to illustrate your issue) and the result you would expect to get from that sample data?

Thursday, February 8, 2018 8:21 PM
• Hi,

I collect in the table evaluation test results. The exams include several examination questions per section. Exam questions are not relevant = 0, successful = 1, and error = 2. Each section can contain two errors. This means that if the note 2 appears more frequently than twice in the columns awa60 to awa63, the entire section has not been passed. So, if the score 2 is occcuring 3 or more the result has to be 1(the whole section has not been passed). This value is then further processed in other measures that evaluate other sections. Hui

Guenter

Best Regards Guenter Prang

Friday, February 9, 2018 7:49 AM
• I collect in the table evaluation test results. The exams include several examination questions per section. Exam questions are not relevant = 0, successful = 1, and error = 2. Each section can contain two errors. This means that if the note 2 appears more frequently than twice in the columns awa60 to awa63, the entire section has not been passed. So, if the score 2 is occcuring 3 or more the result has to be 1(the whole section has not been passed). This value is then further processed in other measures that evaluate other sections. Hui

Ok, so the expression I provided in my previous post should give you a count of all the responses that failed that section. Or you could take the inner IF expression and create a calculated column.

IF( IF( bewertung[awa60] = 2, 1) +
IF( bewertung[awa61] = 2, 1) +
IF( bewertung[awa62] = 2, 1) +
IF( bewertung[awa63] = 2, 1) ) > 2
, 1)

So that you could then use this column in further calculations

Friday, February 9, 2018 2:02 PM

• Best Regards Guenter Prang

Friday, February 9, 2018 2:09 PM

• Best Regards Guenter Prang

Friday, February 9, 2018 2:12 PM
• Hi Darren,

It is to despair.

Best Regards Guenter Prang

Friday, February 9, 2018 2:13 PM
• Hi,

Why are you using ";" instead of "," ?

Darren's expression is with ",", not ";" :

IF( IF( bewertung[awa60] = 2, 1) +
IF( bewertung[awa61] = 2, 1) +
IF( bewertung[awa62] = 2, 1) +
IF( bewertung[awa63] = 2, 1) ) > 2
, 1)

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

[User Page]     [MSDN Page]     [Blog]     [Linkedin]

Friday, February 9, 2018 3:14 PM
• @Ousama - that would just be a localization feature, some countries use ; as a list separator and , as the decimal separator and the PowerPivot UI in Excel supports both styles. (we actually have a button in the tool bar for DAX Studio which will swap between the two styles to make it easier for people to use code samples that are in the other localization format)

@Guenter - I think I just missed including an opening "(" character in front of the inner IF statements. I created some dummy data and used the following formula and it seems to be working fine as far as I can tell

=IF( (IF( bewertung[awa60] = 2; 1) +
IF( bewertung[awa61] = 2; 1) +
IF( bewertung[awa62] = 2; 1) +
IF( bewertung[awa63] = 2; 1) ) > 2
; 1)