Team Referrals from the context of each team

[BINewbie1]

Hello,

I'm trying to create a report that based on a simple referral process between teams, for example, say there are three teams and the refer clients between them. There's a receiving team, and what I've called the actioning team - data is logged by the team that makes the referal, the actioning team. Each team referrs clients to the other two teams.

I've trying to create a tables for each team that would show the number of referrals made to the other teams, with the team names listed in that table. I'm finding it hard to create a filter context that will show referals in both directions for each team. I can count them easily enough, but not list out the actioning team and receving team for each of the different teams. Hope that makes sense - example of what I need at the bottom.

The Referrals Data is in this format, and it using a Team ID to connect through relationship to a single team dimension table:

Referral ID	Actioning Team	Receiving Team
1	A	B
2	B	C
3	C	A
4	A	C
5	B	A

My current data model is fairly simple (but maybe wrong) with two inactive relationships between the Teams Dimention table, and the Referrals fact table.

Here's the pbix file:

From - To Test.pbix 

What I want to get to will look like this, a income and outgoing table from the perspective of each team. There's only 5 referrals, but separated into the Team Context, the sum total would be 10, due to the double counting amungst the receiving and actioning teams.

Team Alpha (Incoming)		Team Alpha (Outgoing)
Team Name (Actioning)	No.		Team Name (Receiving)	No.
Bravo	1		Bravo	1
Charlie	1		Charlie	1
Team Bravo (Incoming)		Team Bravo (Outgoing)
Team Name (Actioning)	No.		Team Name (Receiving)	No.
Alpha	1		Alpha	1
Charlie	0		Charlie	1
Team Charlie (Incoming)		Team Charlie (Outgoing)
Team Name (Actioning)	No.		Team Name (Receiving)	No.
Alpha	1		Alpha	1
Bravo	1		Bravo	0

Not sure whether the answer is in the data modelling, or a clever filter context in DAX? I think a merged query between the tables would probably would, so that I could use the new columns (with the team names) in the tables - but merged queries always seems to slow down the report, and create refresh errors - so I'd like another way.

Many thanks,

Adam

[johnt75 · Accepted solution]

You need another copy of the Teams table, which you can make like

Teams for slicer = Teams

Create the same inactive relationships from this new table to the Referrals table, and use this table in the slicer.

Create a calculation group with 2 calculation items,

Incoming = CALCULATE(
    SELECTEDMEASURE(),
    USERELATIONSHIP( 'Teams for slicer'[Team ID], Referrals[Receiving Team] ),
    USERELATIONSHIP( Teams[Team ID], Referrals[Actioning Team] )
)

Outgoing = CALCULATE(
    SELECTEDMEASURE(),
    USERELATIONSHIP( 'Teams for slicer'[Team ID], Referrals[Actioning Team] ),
    USERELATIONSHIP( Teams[Team ID], Referrals[Receiving Team] )
)

and finally create a measure like

Num referrals = COUNTROWS( Referrals )

Put the name from the original Teams table in a matrix with the [Num referrals] measure and the put the calculation group on the columns.

See the attached PBIX for a sample.

[BINewbie1]

Thanks @johnt75,

That looks good. I'll spend some time getting my head around it, but it works as needed.

I was looking for a reason to get into Calulation Groups, which I've not looked at yet.

Many thanks,

Adam

[bhanu_gautam]

@BINewbie1 , Create measures to count the incoming and outgoing referrals for each team.

Outgoing Referrals =
CALCULATE(
COUNTROWS(Referrals),
USERELATIONSHIP(Referrals[Actioning Team], Teams[Team ID])
)

// Measure for counting incoming referrals
Incoming Referrals =
CALCULATE(
COUNTROWS(Referrals),
USERELATIONSHIP(Referrals[Receiving Team], Teams[Team ID])
)

Create tables to display the incoming and outgoing referrals for each team.

DAX
// Table for Incoming Referrals
Incoming Referrals Table =
SUMMARIZE(
Referrals,
Teams[Team Name],
"No. of Incoming Referrals", [Incoming Referrals]
)

// Table for Outgoing Referrals
Outgoing Referrals Table =
SUMMARIZE(
Referrals,
Teams[Team Name],
"No. of Outgoing Referrals", [Outgoing Referrals]
)

[BINewbie1]

Hi @bhanu_gautam,

Thanks for your assistance.

I should have said, one of the complications I have from the actual data model is that there are about 60 teams, and If its possible, i'd rather not have to write 120 measures, and add 60 tables to my model.

Is there a way of writting a pair of generic measures where by the filter context (either a filter or a slicer) would allow the team to be selected and show that team's incoming and outgoing referals. I guess it would all be done in virtual tables, which will be ok.

Many thanks,

Adam