The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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.)
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 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 |
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.)
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |