Forum Discussion

RichBurdick's avatar
RichBurdick
Helper I
6 years ago

Performing Counts

I am trying to get counts for a visualization.  I have audit data in the AuditTable and observation data in the ObservationTable and I am trying to get a count of Critical, Major and Minor observations per audit.

 

I was able to get the counts I was looking for by using the formula below adjustinf for each classification but becasue I am using a filter in the formula audits without an observation are not appearing in my visualization.

 

Critical = CALCULATE(COUNT(ObservationTable[CLASSIFICATION_OF_OBSERVATION]),FILTER(ObservationTable, ObservationTable[CLASSIFICATION_OF_OBSERVATION] = "Critical"))

 

Ideally I would like to have all audits appear regardless  if they have a Critical, Major or Minor observation.  I would also like a 0 inserted if that classification is not part of that audit.

13 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi RichBurdick 

    try ALLEXCEPT()

    Count_By_Classification = 
CALCULATE(COUNTROWS(ObservationTable), ALLEXCEPT(ObservationTable, ObservationTable[CLASSIFICATION_OF_OBSERVATION]) )

    then just filter it in visual or with slicer, it depends how do you want your desired result should look like

    • RichBurdick's avatar
      RichBurdick
      Helper I
      6 years ago

      Hi az38 

       

      For my visualization I need to have a column for Critical, Major and Minor in my table.

      • az38's avatar
        az38
        Community Champion
        6 years ago

        RichBurdick 

        maybe it will be enough to set inactive parameter "Show items with no data" in visual settings?

        for 0 in empty cell use statement like

        Critical = 
var _count = CALCULATE(COUNT(ObservationTable[CLASSIFICATION_OF_OBSERVATION]),FILTER(ALL(ObservationTable), ObservationTable[CLASSIFICATION_OF_OBSERVATION] = "Critical"))
return
if(_count > 0, _count, 0)

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Try this measure:
    Observation Count = COUNTROWS( ObservationTable )

    and slice by your attributes (Critical, Major, Minor) in a matrix.

    Best
    D
    • RichBurdick's avatar
      RichBurdick
      Helper I
      6 years ago

      Thank you az38 and Anonymous  for your advice.  I realized I was missing something so I created a new field to help with the calculation.

       

      In the AuditTable the important fields are TRN_ID, and ChildObs

      In the ObservationTable the important fields are PARENT_ID, CLASSSIFICATION_OF_OBSERVATION

       

      Ideally what I need is three calcualted columns for Critical, Major and Minor.

       

      The logic for the calculation is:

      If ChildObs = False = 0

      If ChildObs = True and OBSERVATION_CLASSIFICATION = Critical, Count

      If Not 0