cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElvirBotic
Helper I
Helper I

Need help counting records in specified date range

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?

1 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.)

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

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

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 IDBuilding IDInspection DateTemplate Used
100108/24/2021Quarterly
200504/06/2021Annual
300305/20/2021Daily
400712/30/2020Annual
500609/12/2021Quarterly
600109/01/2021Weekly
700407/13/2021Quarterly
800609/12/2021Quarterly
900909/03/2021Daily
1001007/13/2020Quarterly

See attached for two different approaches.

 

 

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. 

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

View solution in original post

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.