Forum Discussion
Helper IIICount Inspections for each Location Once and only Once
I have a dataset where I need to count completed Inspections for each location and count it only once. I will create a table of mock data I am working with. The users will inspect different locations. Each quarter a location needs to complete 1 Long inspection and 2 short inspections. There are Instances where users will complete a long inspection twice in a quarter and we do not want it to count more than once. Same goes for short inspections. If the user completes more than 2 short inspections we only want to count two for each location. I hope that clarifys things. If I can answer anymore questions I would be glad to.
Data:
| SiteID | Date Completed | Inspection Type |
| 1 | 01/15/2022 | Long |
| 1 | 01/16/2022 | Long |
| 2 | 02/11/2022 | Short |
| 3 | 04/04/2022 | Short |
| 1 | 03/11/2022 | Short |
| 2 | 02/06/2022 | Short |
| 1 | 02/15/2022 | Long |
| 2 | 03/04/2022 | Short |
Result:
| Site | Type | Total | Quarter |
| 1 | Long | 1 | 1 |
| 1 | Short | 0 | 1 |
| 2 | Long | 0 | 1 |
| 2 | Short | 2 | 1 |
| 3 | Long | 0 | 1 |
| 3 | Short | 1 | 2 |
4 Replies
johnt75Super User
Try
Total inspections =
var numInspections = CALCULATE( COUNTROWS('Data') )
var inspectionType = SELECTEDVALUE('Data'[Inspection type])
var result = IF( inspectionType = "Long", IF( numInspections > 1, 1, numInspections),
IF( numInspections > 2, 2, numInspections) )
return result
ElvirBoticI tried your approach and when I add the measure to a matrix visual my totals row is 1, which is not correct.
- johnt75
Rename the original measure to Individual Inspections and then create a new measure as
Total Inspections = IF( ISINSCOPE( 'Data'[Site]), [Individual Inspections], SUMX( ADDCOLUMNS( SUMMARIZE( 'Data', 'Data'[Site], 'Data'[Type]), "@value", CALCULATE( [Individual Inspections] ), [@value] ) )