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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jacqee
Frequent Visitor

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

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

Jacqee_0-1716558351748.png

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

Jacqee_1-1716558401467.png

 

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.

Jacqee_2-1716558587874.png

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!!!

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





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

3 REPLIES 3
_AAndrade
Super User
Super User

HI @Jacqee,

I'm attaching a pbix file with my solution.

The final outout will be this:

_AAndrade_0-1716562330688.png





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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!!

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.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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