Need help counting records in specified date range

[ElvirBotic]

Hello, I have a data model where there is a table that lists all of the inspections conducted by site. Each site needs to complete 1 inspection every quarter using the specified quarterly template. There are roughly 950 sites. I need to be able to count how many of those 950 sites have completed an inspection using the "quarterly" template and has done it within the current quarter for 2021. I have a dimension for "sites" and "templates" and one fact table being the "inspections" table. 

How would I do this?

[lbendlin]

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good).

[ElvirBotic]

From the table below you will see I only want to count records that have one and only one inspection using the "Quarterly" template. In this case the result should be 3. Someone can complete the same inspection using the same template more than once, but I only want to count it once. You will see building 006 has two in Q3 and I only counted it once. Let me know if that clarifies it.

Inspection ID	Building ID	Inspection Date	Template Used
1	001	08/24/2021	Quarterly
2	005	04/06/2021	Annual
3	003	05/20/2021	Daily
4	007	12/30/2020	Annual
5	006	09/12/2021	Quarterly
6	001	09/01/2021	Weekly
7	004	07/13/2021	Quarterly
8	006	09/12/2021	Quarterly
9	009	09/03/2021	Daily
10	010	07/13/2020	Quarterly

[lbendlin]

See attached for two different approaches.

[ElvirBotic]

Could you explain the DAx measure a little? I have some dim tables I need to useSuch as a date dim and building dim. I tired using your DAX and got a sumx error because it can't aggregate my data type. 

[lbendlin · Accepted solution]

Measure = 
var a = SUMMARIZE('Table','Table'[Building ID],'Table'[Quarter],"Has Quarter",if(COUNTROWS('Table')>0,1,0))
return sumx(a,[Has Quarter])

The measure first collects all unique combinations of Building IDs and Quarters for the current filter context.

It then adds an aggregated column that looks if there are any quarterly inspections for that filter context.  (This assumes that you have the Template used = Quarterly filter set in Power BI. Alternatively you could includ that in the calculation .

And finally it tallies up the results for the totals via the SUMX.

The way the measure is written it will work for all four scenarios in the matrix visual (cell,column total, row total, grand total). 

(Think like the Grand Total. Very often measures designed for the Grand Total will also work for the Row and Column Totals and for the individual cells.)

[ElvirBotic]

Okay, I was confused with the "Has Quarter" piece. SInce I do have a date dim with quarter as one of the coumns should I use the CALCULATE function for this? Also, I am not filtering out all other "Templates" I am reporting on all of them it is just that the requirement is to make sure all locations complete one inspection using the "Quarterly" template once a quarter. 

So be able to count how many were done for the quarter by template and be able to easily see who/which location has not done their quarterly inspection.