DAX Query Help RRS feed

  • Question

  • What I am trying to do is create a calculated measure that takes an average of all cases where a condition is met.  Currently the dax formula is set to count rows – is there an easy way to modify it so it takes an average of all the rows that meet the criteria?


    Here is the current formula:

    m ClosedTickets = IF(COUNTROWS(VALUES('Sheet1 (2)'[Month]))=1, COUNTROWS(FILTER(IncidentSet, IncidentSet[Ticket Month (Closed)] = VALUES('Sheet1 (2)'[Month]) && 'IncidentSet'[StateCode.Value] = 1)))


    I imagine the new formula should be something like:

    IF(COUNTROWS(VALUES('Sheet1 (2)'[Month]))=1, Average(IncidentSet[Days Open](FILTER(IncidentSet, IncidentSet[Ticket Month (Closed)] = VALUES('Sheet1 (2)'[Month]) && 'IncidentSet'[StateCode.Value] = 1)))


    However, I get an error that “The function used in this expression is not a calid function nor the name of a measure.

    Wednesday, August 12, 2015 6:50 PM


  • Well, AVERAGE is a valid DAX function.

    You are missing a "," after Average(IncidentSet[Days Open]"

    Or you need to close you parenthesis. However, you will need to use AVERAGEX instead of AVERAGE in your case.

    Wednesday, August 12, 2015 7:19 PM