Complicated Table Links

[Anonymous]

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!

[v-huizhn-msft · Accepted solution]

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

[Anonymous]

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.