none
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

Answers

  • 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.

    https://msdn.microsoft.com/en-us/library/ee634546.aspx


    Wednesday, August 12, 2015 7:19 PM