none
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

    Wednesday, February 07, 2018 7:56 AM

All replies

  • Hi Guenter,

    Thanks for your question.

    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 07, 2018 8:42 AM
    Moderator
  • 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 07, 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

    Wednesday, February 07, 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 Answered"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 07, 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 08, 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 Answered "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 08, 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 08, 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?


    http://darren.gosbell.com - please mark correct answers

    Thursday, February 08, 2018 8:21 PM
    Moderator
  • 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 09, 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


    http://darren.gosbell.com - please mark correct answers

    Friday, February 09, 2018 2:02 PM
    Moderator

  • Best Regards Guenter Prang

    Friday, February 09, 2018 2:09 PM

  • Best Regards Guenter Prang

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

    It is to despair.


    Best Regards Guenter Prang

    Friday, February 09, 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 Answered "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 09, 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)


    http://darren.gosbell.com - please mark correct answers

    Friday, February 09, 2018 10:38 PM
    Moderator