cancel
Showing results for
Did you mean:
Frequent Visitor

The example file is here

I am trying to calculate the sum of Usage_Table[Qty]

WHERE

Usage_Table[Month] = Calendar[MonthData]

AND

Usage_Table[Part] = Receiving_Table[Part]

WHERE

Receiving_Table[Month] = Calendar[MonthData]

The Month to MonthData is a Many-To-Many Relationship.

Part is also a Many-To-Many Relationship.

The problem is that PowerBi doesn't like circulated reference nor many to many relationships.

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

Hi @optimusprime,

The many to many relationship is unsupported in Power BI. For your scenario, you canget a integrated table by inner join function , then usethe integrated table to create the report. Please follow the steps below.

1. Create AA table using the formula.

```AA =
FILTER (
CROSSJOIN (
FILTER (
CROSSJOIN ( Receiving_Table, Usage_Table ),
Receiving_Table[Part1] = Usage_Table[Part]
),
'Calendar'
),
'Calendar'[MonthData_C] = Receiving_Table[Month1]
)
```

2. Create Table 'BB' based on 'AA'.

```BB =
SELECTCOLUMNS (
AA,
"Date", AA[Month1],
"Usage_Month", AA[Month],
"Calendar_month", AA[Month_C],
"Part_M", AA[Part],
"Qty_Usage", AA[Qty],
"Qty_Receiving", AA[Qty1]
)
```

3. Create the integrated table use the formula.

`CC = FILTER(CROSSJOIN('Calendar',BB),'Calendar'[MonthData_C]=BB[Date])`

4. Create measure using the formula.

`Result = SUM(CC[Qty_Usage])`

Best Regards,
Angelia

2 REPLIES 2
Frequent Visitor

I am trying to calculate the sum of Usage_Table[Qty]

WHERE

Usage_Table[Month] = Calendar[MonthData]

AND

Usage_Table[Part] = Receiving_Table[Part]

WHERE

Receiving_Table[Month] = Calendar[MonthData]

The Month to MonthData is a Many-To-Many Relationship.

Part is also a Many-To-Many Relationship.

The problem is that PowerBi doesn't like circulated reference nor many to many relationships.

I attached the example file in next reply.

Super Contributor

Hi @optimusprime,

The many to many relationship is unsupported in Power BI. For your scenario, you canget a integrated table by inner join function , then usethe integrated table to create the report. Please follow the steps below.

1. Create AA table using the formula.

```AA =
FILTER (
CROSSJOIN (
FILTER (
CROSSJOIN ( Receiving_Table, Usage_Table ),
Receiving_Table[Part1] = Usage_Table[Part]
),
'Calendar'
),
'Calendar'[MonthData_C] = Receiving_Table[Month1]
)
```

2. Create Table 'BB' based on 'AA'.

```BB =
SELECTCOLUMNS (
AA,
"Date", AA[Month1],
"Usage_Month", AA[Month],
"Calendar_month", AA[Month_C],
"Part_M", AA[Part],
"Qty_Usage", AA[Qty],
"Qty_Receiving", AA[Qty1]
)
```

3. Create the integrated table use the formula.

`CC = FILTER(CROSSJOIN('Calendar',BB),'Calendar'[MonthData_C]=BB[Date])`

4. Create measure using the formula.

`Result = SUM(CC[Qty_Usage])`