Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BeardedCreeker
New Member

Calculating the number of inspections completed by an individual

I have two tables. One contains data related to inspections completed such as the Primary inspector, date, inspection number, and some other data that does not matter in this case. I need to count the inspections completed but I only want to count the ones that were completed by senior leaders in the organization. My second table contains 2 columns which lists the senior leaders, with their name in one column and their department in the other.

 

I had previously counted them using the following and then in the visual I filtered using the list of leaders and the relationship as a means to accomplishing my count, however i want to also display the inspections by leaders and another column showing inspections by non-leaders and that method of filtering won't work anymore.

 
Inspections = CALCULATE(
COUNT('Inspections'[Inspection ID]),
FILTER(ALLSELECTED('Dates'), 'Dates'[Date] <= Max('Dates'[Date])))
 
Inspections Table
Inspection IDPrimary InspectorDateOfficeRegion
1234Fred K02-10-2021EdmontonNorthern Alberta
2345Mike G03-02-2021CalgarySouthern Alberta
3456Sheala K03-15-2021GuelphOntario
4567Steph R04-10-2021Fort St. JohnBritish Columbia

 

Leaders Table

LeaderDepartment
Fred KManufacturing

Mike G

Purchasing
Steph RWarehouse
Frank LOffice Services

 

Any help would be greatly apreciated. This seems like it should be fairly easy but I cant get it to work for the life of me.

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@BeardedCreeker 

To get the leaders count:

leaders count = 
COUNTROWS(
    FILTER(
        SUMMARIZE(Inspections,Inspections[Inspection ID],Inspections[Primary Inspector]),
        Inspections[Primary Inspector] in VALUES(Leaders[Leader])
    ) 
)    

Non Leaders Count:

Non leaders count = 
COUNTROWS(
    FILTER(
        SUMMARIZE(Inspections,Inspections[Inspection ID],Inspections[Primary Inspector]),
        NOT Inspections[Primary Inspector] in VALUES(Leaders[Leader])
    ) 
)    
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

1 REPLY 1
Fowmy
Super User
Super User

@BeardedCreeker 

To get the leaders count:

leaders count = 
COUNTROWS(
    FILTER(
        SUMMARIZE(Inspections,Inspections[Inspection ID],Inspections[Primary Inspector]),
        Inspections[Primary Inspector] in VALUES(Leaders[Leader])
    ) 
)    

Non Leaders Count:

Non leaders count = 
COUNTROWS(
    FILTER(
        SUMMARIZE(Inspections,Inspections[Inspection ID],Inspections[Primary Inspector]),
        NOT Inspections[Primary Inspector] in VALUES(Leaders[Leader])
    ) 
)    
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.