none
If Statement wipes out my calculate measure

    Question

  • I'm still at it, slugging away. I working at finding the fastest response time of fire trucks on an incident. 

    It looks like it is giving me the lowest time for each incident. 

    I want to run a simple if statement, but it wipes out my calculate column and it doesn't look like it even works. I'm wondering if I have to iterate through each incident with the if statement? But, since my measure already does that, it would l seem I don't. 

    Very lost at this moment. Thanks for looking. Brent

    Saturday, August 16, 2014 9:14 PM

Answers

  • Hi Brent,

    The solution provided takes advantage of the filter propagation characteristics of CALCULATE. In other words, the COUNTROWS function is evaluated within the context of the current row's IncidentKey and so the count only includes rows in the apparatus table that are related to that incident and not for all rows in the apparatus table. The behaviour actually matches the example image that you provided.

    In the screenshot below I have filtered the Power Pivot window to show a single row in the incident table so that we can compare this result with the corresponding rows in the apparatus table. The 'ObjectiveMet' column is based on the last DAX solution that I provided, and the 'ObjectiveMetRowCount' shows you what the COUNTROWS within the CALCULATE is returning before we convert it into a TRUE/FALSE value by checking if it is greater than 0 (i.e. at least 1). 'ObjectiveMetRowCount' has been defined as:

    =CALCULATE(
      COUNTROWS(apparatus), 
      apparatus[Response Times Seconds] < 320,
      uv_901ACTION[Top_Category]="Fire",
      uv_901APP_TYPE[Description]="Engine"
    )

    Notice that the 'ObjectiveMet' column has a value of 'TRUE' and the 'ObjectiveMetRowCount' has a value of '1'.

    Here are the corresponding rows from the apparatus table. Notice that only one of the rows for the matching IncidentKey has a response time value below 320 seconds.  Also, the other two rows would be eliminated from the count even if their response times were below 320 because they are not for 'Engine'.

    Is this what you would expect to happen?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Monday, August 18, 2014 9:34 PM
    Moderator

All replies

  • As I read about IF(values) - http://www.powerpivotpro.com/2011/03/the-magic-of-ifvalues/

    I'm wondering if the Values() doesn't allow a measure?

    Saturday, August 16, 2014 10:33 PM
  • I  thought I would try a different approach. My incident table has one row for each incident. 

    Sunday, August 17, 2014 2:31 AM
  • Although, I think I know why my initial If statement did not work (I did not wrap it in an aggregate function), I'm still working on the overall solution. I admit it has the best of me now, but I'll keep at it. 

    =COUNTROWS(FILTER(VALUES(incident[incidentkey]),
                             [CalculateRound Min Response Time]<320))

    The above returns a pivot, but not what I'm after. At least it feels get to not see an error message. 

    Thanks

    Sunday, August 17, 2014 9:39 PM
  • Hi bvanscoy678,

    If you could kindly provide a set of test data, and restate your overall aim, it may be possible for members of the forum or myself to come up with a working solution. Could you give examples of how the output should look? A mock-up in Excel to help illustrate the expected outcome based on the test data would be great.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Monday, August 18, 2014 1:21 PM
    Moderator
  • Hello Michael,

    I'm sorry for the unclear question. Hopefully, the attached workbook will shed some light on what I'm trying to do. My overall aim to to evaluate response times to see if at least one ENGINE arrived under 320 seconds to a fire call. My unique identifier for an incident is the incidentkey which is located in both the Incident table and the apparatus table. There is one incidentkey in the incident table, but multiple keys in the apparatus table. The apparatus table contains each unit that responded to the incident and their response time (Arrival date minus dispatched date). 

    My thoughts were to measure the response times in the apparatus table and those results to provide a TRUE (met objective) or FALSE (missed objective) for each incident. Because the incident table contains 1 row for each incident, if I could assign a TRUE/FALSE for each row, that will give me the ablity to produce a percentage of how many incidents we met each month. 

    I apologize for me cluttered notes, but this is all fairly new (powerpivot) to me and I'm working through the syntax, row versus table context, but I'll keep at it. 

    Thank you for any guidance. Brent

    Data and also a "mock up" sheet. 

    https://www.dropbox.com/s/zyybv9uakxefnhl/Forum%20Book%20ver%202.xlsx

    Monday, August 18, 2014 1:56 PM
  • If I've understood you correctly then creating a calculated column on your incident table using the following DAX formula should do what you're asking:

    =
    IF(
      CALCULATE(
        COUNTROWS(apparatus), 
        apparatus[Response Times Seconds] < 320
      ) > 0, 
      TRUE(), 
      FALSE()
    )

    My understanding is that if at least one Engine (i.e. apparatus) responded within 320 seconds then the incident met the objective, otherwise it didn't. If this isn't right please let me know which parts of your requirement I may have misunderstood.

    Edit: By the way, thanks for providing the refined explanation and a good sample workbook to work with :)

    Just took a look at your other related post and it looks like the complete DAX formula for correctly flagging incident rows that were responded to by fire engines within 320 seconds could be...

    =
    IF(
      CALCULATE(
        COUNTROWS(apparatus), 
        apparatus[Response Times Seconds] < 320,
        uv_901ACTION[Top_Category]="Fire",
        uv_901APP_TYPE[Description]="Engine"
      ) > 0, 
      TRUE(), 
      FALSE()
    )


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Monday, August 18, 2014 2:53 PM
    Moderator
  • Hello,

    I'm getting an error trying to use the measure [Response Times Seconds]  ''=86400*( [arrivaldate]-[dispatchdate])''

    As a look at the measure (your second example) this will return a row count (apparatus table)  for every engine that responded to a call <320 seconds. Because there are multiple Engines that respond to a fire, I would get a count of each Engine, not for the overall Incident (If I read it correctly) status. 

    I altered the statement (of course it's wrong) as a general idea. 

    =
    IF(
      CALCULATE(
        COUNTROWS(incident), 
        IF at least 1 Engine [Response Times Seconds] < 320,
        uv_901ACTION[Top_Category]="Fire",
        uv_901APP_TYPE[Description]="Engine"
      ) > 0, 
      TRUE(), 
      FALSE()
    )

    Also, I thought this mock up might help explain the problem. I've worked all day on this and I have better idea, but I'm not there yet. 

    Thanks for working on it. I greatly appreciate the help and this gives me another idea. Brent

    Monday, August 18, 2014 8:47 PM
  • Hi Brent,

    The solution provided takes advantage of the filter propagation characteristics of CALCULATE. In other words, the COUNTROWS function is evaluated within the context of the current row's IncidentKey and so the count only includes rows in the apparatus table that are related to that incident and not for all rows in the apparatus table. The behaviour actually matches the example image that you provided.

    In the screenshot below I have filtered the Power Pivot window to show a single row in the incident table so that we can compare this result with the corresponding rows in the apparatus table. The 'ObjectiveMet' column is based on the last DAX solution that I provided, and the 'ObjectiveMetRowCount' shows you what the COUNTROWS within the CALCULATE is returning before we convert it into a TRUE/FALSE value by checking if it is greater than 0 (i.e. at least 1). 'ObjectiveMetRowCount' has been defined as:

    =CALCULATE(
      COUNTROWS(apparatus), 
      apparatus[Response Times Seconds] < 320,
      uv_901ACTION[Top_Category]="Fire",
      uv_901APP_TYPE[Description]="Engine"
    )

    Notice that the 'ObjectiveMet' column has a value of 'TRUE' and the 'ObjectiveMetRowCount' has a value of '1'.

    Here are the corresponding rows from the apparatus table. Notice that only one of the rows for the matching IncidentKey has a response time value below 320 seconds.  Also, the other two rows would be eliminated from the count even if their response times were below 320 because they are not for 'Engine'.

    Is this what you would expect to happen?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Monday, August 18, 2014 9:34 PM
    Moderator
  • That looks like it is it!

    I think the biggest part I was missing was the part about the filter working in context of the incidentkey of the incident table. I'm going to print all this out tomorrow at work and step through all the syntax and make sure I fully understand it. The calculated columns makes sense and I created some very quick and basic pivots that look correct. I'll need to look at the measure and make sure I fully comprehend it. 

    Thanks for sticking with me on this one. Your solution seems much simpler than I envisioned and that might be part of the problem. 

    Thanks! Brent

    Tuesday, August 19, 2014 12:02 AM
  • Michael,

    I was able to finally get a chance to look at the calculated column and the measure and I understand it now. Your If( ) function is based off the returned value of countrows () from the calculate function. The calculate function has 3 filters. It seems so simple now, but the lesson will go a long ways in other objectives I am working on. I went from not even coming close to understanding it, to a full comprehension and something I can build upon. 

    Again, thank you for taking the time in giving me such a fantastic summary of my problem!

    Thanks,
    Brent

    Tuesday, August 19, 2014 11:32 PM
  • Hi Brent,

    Glad that it helped :)


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Wednesday, August 20, 2014 5:59 AM
    Moderator