Forum Discussion
Where to read about RELATED/relations? - problem example
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!
Anonymous,
You may add a measure as follows.
Measure = DIVIDE ( COUNT ( Table_A[client] ), MAXX ( Table_A, RELATED ( Table_B[employees] ) ) )
7 Replies
Hi,
Share some data and show the expected result.
Hi,
Try this
- Create a relatisnhip from the CallCentre column of Table_A to the CallCentre column of Table_B
- In your visual, drag the CallCentre column from Table_B
- Write these measures
Calls=COUNTROW(Table_A)
Rate=[Calls]/MIN(Table_B[Employees])
Forma the Rate measure as a %.
Hope this helps.
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+russoRegards,
Tom
TomMartens will look for it, thank you!
