Hello! I'm quite new to PBI and I'm trying to resolve this issue. It'd be great if any of you could help.

I have two tables setup as below, and I need to create a visual to show the failure rate according to different parameters.

Example:

- Brake system Failure Rate of operator A for Ferrari in Apr 2017 = number of brake system failures / number of hours

- Pneumatic system Failure Rate of operator B for Porche in June 2017 = number of pneumatic system failures / number of cycles

So far I've created a measure called "Count" as Count = CALCULATE(COUNTROWS(),ALLEXCEPT('Table 1','Table 1'[OPERATOR],'Table 1','Table 1'[Date],'Table 1','Table 1'[Car Model],'Table 1','Table 1'[Failure Category])

This effectively created a multidimensional table that if interrogated (I've created a Matrix and setup different data slicers just to check if the above worked) gives me the right count of failures per month, or per operators, or per car models, etc...

I now need to somehow calculate the rates by dividing "Count" by either number of hours or number of cycles (maintaining the correct operator, failure category and date).

I've tried to create relationships between the two tables through other simple 3 tables that contain dates, car models or operators data only once, in order to relate Table 1 with those 3 tables, than those 3 tables with Table 2. However Power BI gives me an error that I can't create as many relationships due to ambiguity.

I'm a bit lost. Anyone knows how I can solve this? Thank you in advance!

Table 1

Operator	Date	Car model	Failure Category
A	Apr-17	Ferrari	Brakes
B	May-16	Porche	Engine
C	Feb-16	GMC	Hydraulic
D	Jan-14	Porche	Electric
E	Apr-17	BMW	Brakes
A	May-16	GMC	Hydraulic
A	Feb-16	BMW	Engine
C	Jan-14	GMC	Brakes
D	Apr-17	Porche	Engine
E	May-16	BMW	Hydraulic
E	Feb-16	GMC	Engine
B	Jan-14	BMW	Brakes
A	Apr-17	Porche	Engine

Table 2

Operator	Date	Monthly Utilization (hours)	Monthly Utilization (cycles)
A	Apr-17	2500	120
B	Apr-17	1324	764
C	Apr-17	5637	761
A	May-17	745	450
B	May-17	8463	770.5
C	May-17	9457	869.2
A	Jun-17	4546	967.9
B	Jun-17	2987	1066.6
C	Jun-17	3526	1165.3

Forum Discussion

Rate between tables

Utilization Rate

Currency FX Rates

Adding an intererst rate

Exchange Rates - Historical

DAX Measure for Ranking the valid rows and calculating the average rating