Counting employees in multiple columns based on item aging and age groups

[Jacqee]

I have a large data set where each row is for an item.  The item can have any combination of 7 different types of testing.  All types of testing have a colum for when it was sent to testing, when it finished testing and the employee who tested it.  So a total of 21 columns just for this data.  I've added columns to calculate the aging for each type of testing if there is no finish date.  Then there are columns to group the aging columns into Aging Groups.

I want to set up a visual to display the could of types of testing by aging groups and then add a drill down to add in the Employee names counting now many of each type of testing they have in each age group.

Example of data with only 2 types of testing

I'd like the end result to be something like this... 

I can get to the types of testing and age groups but when I add in the employees, it changes all numbers to the totals and looks like this.... will all possible employees listed even if they didn't do any testing.

I'm at a loss.  I assume that I need a relationship between the employee table and the data table...but there are 7 possible columns to link them to and I'm not sure how to do that or if it will correct all the issues I'm seeing.

Please and thank you for your input!!!

[_AAndrade]

HI @Jacqee,

I'm attaching a pbix file with my solution.

The final outout will be this:

[Jacqee]

Thank you so much!  However I've not been able to replicate your work.  Some differences, the full dataset has some items that do not have all of the 7 types of testing performed so those fields are left blank.  Since they don't all have the testing, I'm not able to use the "Closed" Group Name.  I'm only using IDs 1, 2, and 3.  I'm filtering out the blanks when I create the visuals.

I'm able to create the 1st count based on your "Count SA".  I have a version of the "Count BT" which seems to work.  Could you provide some additional inforamtion around that measure?

Count BT =

CALCULATE(

    COUNT(f_data[TesterSA]),

    d_Groups[ID] <> 1,

    USERELATIONSHIP(d_Groups[ID],f_data[BT_Age_Group_ID]),

    USERELATIONSHIP(d_Employees[ID],f_data[ID_TesteBT])

)

Question - is it really meant to count the TesterSA field for the Count BT?  I changed it to count the Item ID.  It seemed to work.  Any future issues I should be aware of with using it?

thank you so much!!

[_AAndrade · Accepted solution]

Hi @Jacqee,

Notes of what this measures is doing: 

Count BT =

CALCULATE(

    COUNT(f_data[TesterSA]),  -- Count number of Tester (should be Tester BT but is working because I don't have blank or null values in both columns

    d_Groups[ID] <> 1, -- Only use rows that ara not closed

    USERELATIONSHIP(d_Groups[ID],f_data[BT_Age_Group_ID]), -- virtual conection with both tables

    USERELATIONSHIP(d_Employees[ID],f_data[ID_TesteBT]) -- virtual conection with both tables

)

Question - is it really meant to count the TesterSA field for the Count BT?  I changed it to count the Item ID.  It seemed to work.  Any future issues I should be aware of with using it?

As I said before you probably will have the same result but you need to be sure that you don't have null or blank values in the both columns, because count skips this rows. However you could use countrows instead of count and it will count all rows with blank and null values.
I hope you could understand my explanation and could solve your issue.