Where to read about RELATED/relations? - problem example

[Anonymous]

Latest questions I made could be solved by me just understanding relations better in Power BI, I read solutions on forums but don't finish understanding the functions.

As an example, now I am doing multiple measures to calculate this, and I'm sure it can be done in one formula, don't know how, every thing I tryied gave wrong results.

Table_A [client, callCenter, contactDate]

Table_B [callCenter, employees]

I made a 1 to many relation, a Table B callCenter can have many calls (Table A) ON callCenter.

Want to get a rate of contacs/employees.

So the formula will be in the Table_A something like:

rate = DIVIDE(count(Table_A[client]),RELATED(Table_B[employees]))

obviously that doesnt work...still don't understand why.

The table I want to fill with this info has this fields, the functions are selected on the field properties in the table:
Table_A.call_center,count(Table_A.client),rate

The things I tryed with related make the value change if I added the callCenter of both tables or only of one of them, I don't want to add anything of Table_B.

I hope I make myself clear, english is not my primary languaje and I'm new to Power BI and this feels rather complex.

Thank you! 

[TomMartens]

Hey @Anonymous,

in addition to trying the idea provided by @Ashish_Mathur you may find this book by Alberto Ferrari and Marco Russso interesting that delves deep into data modeling:
https://www.amazon.com/Analyzing-Power-Pivot-Business-Skills/dp/150930276X/ref=sr_1_3?ie=UTF8&qid=1536477301&sr=8-3&keywords=marco+russo

Regards,

Tom

[Anonymous]

@TomMartens will look for it, thank you!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Table_A				Table_B
client	callCenter	contactDate	callCenter	employees
6	1	09/08/2018		1	10
6	2	09/08/2018		2	2
6	3	09/08/2018		3	5
5	1	09/08/2018
5	1	09/08/2018
6	1	09/08/2018
5	2	09/08/2018

Expected result table:

call_center	calls	rate	rate calculation
1	4	0,4	(4/10)
2	2	1	(2/2)
3	1	0,2	(1/5)

Thank you!

[Ashish_Mathur]

Hi,

Try this

1. Create a relatisnhip from the CallCentre column of Table_A to the CallCentre column of Table_B
2. In your visual, drag the CallCentre column from Table_B
3. Write these measures

Calls=COUNTROW(Table_A)

Rate=[Calls]/MIN(Table_B[Employees])

Forma the Rate measure as a %.

Hope this helps.

[Anonymous]

On monday I'll have access to try it, and tell you how it went

I already done steps 1 and 2, step 3 sounds familiar, but I tryed too many things, will look into that, it feels strange that one cannot do that in one formula.

Thank you

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may add a measure as follows.

Measure =
DIVIDE (
    COUNT ( Table_A[client] ),
    MAXX ( Table_A, RELATED ( Table_B[employees] ) )
)