locked
Using If(Values) in a measure with MINX - Error RRS feed

  • Question

  • Hello,

    I've retooled my original project, hopefully building upon some additional training this past week.  I'm measuring times for fire department's responses to test if we meet our objectives. I've created a calculated column using (below) to return an integer, which I can test against our goal. 

     =round(24*60*60*(apparatus[arrivaldate]-apparatus[dispatchdate]),1)

    My idea was to find the minimum time of the incident by using MINX([Response Time]) on my apparatus table (it holds times for each unit) with a unique identifier for the overall incident stored in my incident table. Because units are cancelled, this is leaving me with negative numbers in my calculated column. I thought I could test using an IF statement in my measure to only look at times that were greater than 0, but I was wrong. 

    Is it possible to test for the negative number in the measure or am I better off addressing the negative number in the calculated column? My first attempt was to measure for the response time as a measure, so I might revisit that idea. Instead of calculated column, I also tried:

    =minx(apparatus,ROUND(24*60*60*
                         (apparatus[arrivaldate]-apparatus[dispatchdate]),1
                                          ))

    Thanks  for any pointers. I'm new at powerpivot, but I'm working hard at it. 

    Thank you, Brent

    Overall TASK:

    Identify Fire calls (uv_901Action.Code_category = 1)  '<use filter>

    Identify Engines (uv_901App_Type.ActionCode = 11)  '<use filter>

    Measure unit's response times for the incident (on the apparatus table)  '<calculated column and measure>

    Identify if a unit responded under 320 seconds  '<measure>

     

    Below is my model


    • Edited by bvanscoy678 Tuesday, August 12, 2014 1:01 AM comment
    Tuesday, August 12, 2014 12:41 AM

Answers

  • Hi bvanscoy678,

    Haven't had a chance to syntax check this properly but in theory it should give you the same result and possibly better performance:

    =
    CALCULATE(
      MIN(apparatus[Response Time]),
      apparatus[Response Time] > 1
    )


    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

    • Edited by Michael Amadi Friday, August 15, 2014 4:40 PM
    • Marked as answer by bvanscoy678 Sunday, August 17, 2014 9:22 PM
    Wednesday, August 13, 2014 8:37 PM
  • After all of that, I think I solved my problem with a simple calculate. Unless, anyone sees differently, I think I can close the thread. Thanks. 

    • Marked as answer by bvanscoy678 Wednesday, August 13, 2014 5:03 PM
    Wednesday, August 13, 2014 5:03 PM

All replies

  • I was at least able to figure out a row context of testing each value. 

    if((MINX(apparatus,[Response Time in Seconds])<1,
             BLANK(),
              MINX(apparatus,[Response Time in Seconds])
               ))

    Although it solves my problem of not showing the value, the MINX is still picking it up and messing up my first arriving unit return. 

    I think what I want to do is filter out any incident that doesn't have a response time of 1 second. 
    maybe

    CALCULATE(MINX(apparatus,[Response Time]),
                           FILTER(apparatus,apparatus[Response Time]>1))

     This is what's happening. 

    

    It then messes up my minium time




    • Edited by bvanscoy678 Wednesday, August 13, 2014 3:38 PM
    Wednesday, August 13, 2014 12:43 PM
  • After all of that, I think I solved my problem with a simple calculate. Unless, anyone sees differently, I think I can close the thread. Thanks. 

    • Marked as answer by bvanscoy678 Wednesday, August 13, 2014 5:03 PM
    Wednesday, August 13, 2014 5:03 PM
  • Hi bvanscoy678,

    Haven't had a chance to syntax check this properly but in theory it should give you the same result and possibly better performance:

    =
    CALCULATE(
      MIN(apparatus[Response Time]),
      apparatus[Response Time] > 1
    )


    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

    • Edited by Michael Amadi Friday, August 15, 2014 4:40 PM
    • Marked as answer by bvanscoy678 Sunday, August 17, 2014 9:22 PM
    Wednesday, August 13, 2014 8:37 PM
  • Hi Michael,

    I'm not exactly sure how to vote, but I'll look at the board and  try to figure it out. The reason I used MinX was because I'm interested in the row context of each call. I'll give your suggestion a shot. 

    I did try using just calculate before (without filter) and I had an issue. I must have had the syntax incorrect. 

    The below was what I was using and all the filters are pointed at lookup tables as suggested in the course I took. I"ll revisit my solution with your suggestion. 

    =CALCULATE(MINX(apparatus,[RoundResponse]),
    FILTER(uv_901ACTION,uv_901ACTION[Top_Category]="Fire"),
                           FILTER(apparatus,[RoundResponse]>1), 
    FILTER(incident,incident[priorityresponseflag]=TRUE()),
    FILTER(uv_901APP_TYPE,uv_901APP_TYPE[Description]="Engine")

    )

    Thank you for taking the time to answer. I'm new to working with DAX, so it is a little frustrating at time knowing what I want to do, but not how to do it. 

    Brent




    • Edited by bvanscoy678 Sunday, August 17, 2014 9:34 PM
    Sunday, August 17, 2014 9:24 PM
  • Hello,

    I used your suggestion and I was able to get it to work without using filters! I did need to use MINX, but I think that is what I want to do because I'm iterating through each row, looking for the MIN time of the incident. Your suggestion was very helpful going back and figuring out why it did not work the first time for me. I was using a ) instead of a comma between my filter. 

    Thank you  and I'm off to figure out the second part of my solution! Brent

    Sunday, August 17, 2014 10:32 PM