Complicated Table Links

Hi Anonymous,

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])

Please download the file for more details.

Best Regards,
Angelia