Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure: Calculate and Filter

Hey guys,   could you please help me with the following Measure?    I would like to show the "number of services with major incident" per Business Unit nin a way, that if a service had multiple m...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous,

    Please change your DAX to the following:

    Number of Services with Major Incident = CALCULATE(DISTINCTCOUNT('Incident Report'[Title]),FILTER('Incident Report','Incident Report'[ASPStatusID]=1),
FILTER('Incident Report',(CALCULATE(MIN('Incident Report'[Incident Start Date]))>=CALCULATE(MIN('CALENDAR'[Date])) && CALCULATE(MAX('Incident Report'[Incident Start Date]))<=CALCULATE(MAX('CALENDAR'[Date])+1)||CALCULATE(MIN('Incident Report'[Incident End Date]))>=CALCULATE(MIN('CALENDAR'[Date])) && CALCULATE(MAX('Incident Report'[Incident End Date]))<=CALCULATE(MAX('CALENDAR'[Date])+1)))
      ) + 0


    Moreover, please mask your sensitive information such as account name and password when uploading your screenshots.

    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous,

Please change your DAX to the following:

Number of Services with Major Incident = CALCULATE(DISTINCTCOUNT('Incident Report'[Title]),FILTER('Incident Report','Incident Report'[ASPStatusID]=1),
FILTER('Incident Report',(CALCULATE(MIN('Incident Report'[Incident Start Date]))>=CALCULATE(MIN('CALENDAR'[Date])) && CALCULATE(MAX('Incident Report'[Incident Start Date]))<=CALCULATE(MAX('CALENDAR'[Date])+1)||CALCULATE(MIN('Incident Report'[Incident End Date]))>=CALCULATE(MIN('CALENDAR'[Date])) && CALCULATE(MAX('Incident Report'[Incident End Date]))<=CALCULATE(MAX('CALENDAR'[Date])+1)))
      ) + 0


Moreover, please mask your sensitive information such as account name and password when uploading your screenshots.

Regards,
Lydia

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hey Anonymous,

     

    many thanks for your fast reply!

     

    Could you please help me with 1 last issue? would it be possible to extend the "number of services with major incident" to "number of services with incident"?

     

    So it counts also if a service has an incident that is either major/critical = ASPStatusID1 or ASPStatusID2? 

     

    Currently it's counting the ones, where ASPStatusID=2.

     

    I would really appreciate!

     

    Many thanks,

     

    Adam