Associating a fact table to a dimension table with 2 many to 1 links for staff KPI eval

[AaronD42]

It seems like this should be simple but I'm now at day 3 of banging my head against the desk.

I work in blood banking and we track our phlebotomist performance across many metrics that we're moving into PBI.

I have measures built that work very well for us at an operational level but now my challenge is getting it down to the individual performer level, here's the problem:

I have a single fact table with all the "work performed" this includes every transaction with a blood donor regardless of the donation outcome, as we track events at all stages and not all donations succesfully result in blood being donated.  This table includes the employee ID of the staff that screened the donor (intake,health assesment, vital signs) many of these donors are prevented from donating for various health reasons and this is one thing we track.  I also have an employee ID of the staff who actually tried to draw the blood from the donor, which isn't always succesful.  This is something else we need to track.

I have a single employee dimension table that has their employee ID to link to the fact table of donations, but need to dynamically calculate their success/fail rate at screeening and again with drawing blood but those are two separate many to 1 relationships, of which only one can be active.  I tried duplicating the employee table but lose integrity in filtering if I do that.

I can't provide any real world data because it's all PII or PHI, but I'm happy to give mock examples if it would help.

[Avantika_Thakur · Accepted solution]

Hi @AaronD42 ,
You can try the below approach :
1) Fact table can be connected to Dimension table in the following manner  : 
Donor Screened by Employee (Fact table) -> Employee ID (Dimenision table) - Active Relationship
Donor phlebotomist by Employee (Fact table) -> Employee ID (Dimenision table) - Inactive Relationship
2) Measures can be created as below :

(1)  sum of unsuccesful screens by employee / sum of donors screened by that employee
DIVIDE ( CALCULATE(Count(Donor ID),Donor Succesfully Screened = T) , Count(Donor ID))

(2) sum of unsuccesful donations by employee / sum of total donors drawn (needle in arm) by employee
DIVIDE ( CALCULATE(Count(Donor ID),Donor Succesfully donated=F, USERRELATIONSHIP(DimensionTable[Employee ID], FactTable[Donor phlebotomist by Employee]) ,
CALCULATE(Count(Donor ID), USERRELATIONSHIP(DimensionTable[Employee ID], FactTable[Donor phlebotomist by Employee]))

Hope this helps.
If this answers your query, please accept this as a solution.

Thanks,

Avantika Thakur

[AaronD42]

So these are my current measures:

The below need to use the screenerID to EmployeeID relationship

Donors Screened = CALCULATE(SUM(STAFF_KPI_SCORECARD[Registered]))

Hgb Deferrals = CALCULATE(SUM(STAFF_KPI_SCORECARD[Hgb Deferral]))

Hgb Deferral % = CALCULATE( [Hgb Deferrals]/[Donors Screened])

The below need to use the phlebotomistID to EmployeeID relationship

Venipunctures = CALCULATE(SUM(STAFF_KPI_SCORECARD[venipuncture performed]))

Unsuccessful Draws = CALCULATE(SUM(STAFF_KPI_SCORECARD[No Blood])+SUM(STAFF_KPI_SCORECARD[Quantity Not Sufficient]))

Phleb Loss % = CALCULATE([Unsuccessful Draws]/[Venipunctures])

Where do I apply the USERELATIONSHIP?  Since the screenerID is the primary active relationship and I need to diferentiate only the Phlebotomy ID values, do I just need to apply it to those measures?  Or, is it better to create a new set of specific measures for JUST the phlebotomy section?

I ask the second question because I'm already effectively using these to calculate the operation level values independent of staff performance so I don't want to break one for the sake of the other.

Also I was pointed at the preview feature of using visual based calculations, is that a better/worse/terrible idea compared to this?

[Avantika_Thakur]

Hi @AaronD42 ,
You can apply "USERELATIONSHIP" function in the measures using phlebotomist ID to EmployeeID relationship. 
Either you can apply this function in existing measures or create new set of measures depending on the requirement and which approach helps in validating the expected values.

Thanks,

Avantika Thakur

[Avantika_Thakur]

Hi @AaronD42 ,
You can make use of "USERELATIONSHIP" function in the measures to make the inactive relationship active for that particular KPI calculation of drawing blood.
Pls provide the mock data for Fact and dimension tables to explain further.

Thannks,

Avantika Thakur

[AaronD42]

So the fact table has a row for each donor's attempted donation and all corresponding values during that process, mostly T/F flags:

So a simplified row of the fact table is:

Transaction ID

Donor ID

Donor Screened T/F

Donor Succesfully Screened T/F

Donor Screened by Employee: (as EID in employee table)

Donor Attempted donation: T/F

Donor Succesfully donated: T/F

Donor phlebotomist by Employee: (as EID in employee table)

The employee table is a dimension, and crucially for this task has:
Employee ID

Manager Name (employees need to be grouped by their manager for evaluation collectively or individually)

Employee Name

The measures are:

sum of unsuccesful screens by employee / sum of donors screened by that employee

sum of unsuccesful donations by employee / sum of total donors drawn (needle in arm) by employee

These need to be served in a unified view on the dashboard so both measures calculate either for the region specified, or the manager's sub-team or the individual employee.

Currently I'm presenting those results with guages, line charts and matrices of sums, and it works flawlessly for the operational level (by location) but not by individual staff.

**I have a separate heirarchy and scorecard view for the operational side because work performed at locations isn't the same as work performed by staff because we share staff between locations.