Performing Counts

[RichBurdick]

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.

[Anonymous]

Try this measure:
Observation Count = COUNTROWS( ObservationTable )

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

Best
D

[RichBurdick]

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

[az38]

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]

Hi @az38 

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

[az38]

@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)

[RichBurdick]

@az38 , unfortunately that did not work.  

[az38]

@RichBurdick so, how is your data looks like and whats your desired output? please, give an example

[RichBurdick]

@az38, my data is from two tables.  In my visualization I an using fields from the AudutTable and trying to show count of Observation Classifications from the ObservationTable.

I have attached an example of what I am trying to do in my table.

[az38]

@RichBurdick 

ok. how does look like the origin table ObservationTable? what fields do you use to relationships?

[RichBurdick]

@az38 the field I am calculating my count on is ObservationTable[CLASSIFICATION_OF_OBSERVATION].

The relations ship is a one to many: TRN_ID from Audit_Table to Parent_ID from Observastiontable.

[az38]

@RichBurdick 

AuditTable and ObservationTable are different tables? Am i right? 

If so, how look data in ObservationTable ?

You can also share your pbix-file to cloud like https://uploadfiles.io/

[RichBurdick]

@az38, Yes two separate tables.

I am not sure what you mean by how look the data in ther ObservationTable.

[az38]

@RichBurdick 

what fields ObservationTable contains of?