Getting list of function violation where the employee belongs

DAX DAX Calculations
1

Hi so I have 2 tables connected to each other through Employee ID. One is HR list of employees and second is Violations.

I have created a slicer by using employee’s name so anyone can query a particular employee and they will get count of violations of an employee.

Each employee belongs to a particular function. I want to create a measure which also tells me the number of employees in the same function who have had any kind of violations.

So for Eg - If I have selected Jamie Franco in the slicer and since he belongs to GL Global function, I should also be able to see how many employees in GL Global function had violations

Please see the attached pbix file for an example.

Query - Function.pbix (38.8 KB)

2

@mgoyal,

First, I changed the relationship in the data model between the HR and Violations tables from bidirectional to one directional, one to many. The bidirectional relationship is not necessary here, and can cause problems.

Then created the following two measures, the first one to count employee violations, the second to count function violations for the selected employee’s function::

Violation Count = 

VAR Violator = SELECTEDVALUE( HR[Name] )
VAR NumViolations =
    CALCULATE( 
        COUNTROWS( Violations ),
        FILTER (
            ALL (HR),
            HR[Name] = Violator 
        )
    )

RETURN
IF( NumViolations = BLANK(), 0, NumViolations )

Function Violation Count = 
VAR Violator = SELECTEDVALUE( HR[Name] )
VAR FunctionViolation = LOOKUPVALUE( HR[Function], HR[Name], Violator )

VAR NumFunctionViolations =
    CALCULATE( 
        COUNTROWS( Violations ),
        FILTER (
            ALL (HR),
            HR[Function] = FunctionViolation 
        )
    )

RETURN
IF( NumFunctionViolations = BLANK(), 0, NumFunctionViolations )

Full solution file attached below. Hope this is helpful.

  • Brian

eDNA - employee violations solution.pbix (51.0 KB)

3

Omg thank you so much this is exactly what I was looking for. Many many thanks Brian. This is simply outstanding

4

Thanks - very glad to hear that got you what you needed.

  • Brian