Forum Discussion

ElvirBotic's avatar
ElvirBotic
Icon for Helper III rankHelper III
4 years ago

Count 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...
Need Help
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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
  • ElvirBotic's avatar
    ElvirBotic
    Icon for Helper III rankHelper III
    4 years ago

    I tried your approach and when I add the measure to a matrix visual my totals row is 1, which is not correct. 

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      4 years ago

      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] )
)
      • ElvirBotic's avatar
        ElvirBotic
        Icon for Helper III rankHelper III
        4 years ago

        I had no luck with this 😞