Complicated Table Links

Question

The example file is hereI am trying to calculate the sum of Usage_Table[Qty]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Usage_Table[Month] = Calendar[MonthData]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Usage_Table[Part] =&nbsp;Receiving_Table[Part]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Receiving_Table[Month] =&nbsp;Calendar[MonthData]&nbsp;The Month to MonthData is a Many-To-Many Relationship.Part is also&nbsp;a Many-To-Many Relationship.&nbsp;The problem is that PowerBi doesn't like circulated reference nor many to many relationships.&nbsp;&nbsp;&nbsp;Thank you!

v-huizhn-msft · Accepted Answer

Hi&nbsp;Anonymous,The many to many relationship is unsupported in Power BI. For your scenario, you canget a&nbsp;integrated table by inner join function , then usethe integrated table&nbsp;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&nbsp;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

Forum Discussion

Complicated Table Links

2 Replies

Related content

Complicated IF

Extracting complicated text from a column

Dataverse Virtual table link to Fabric

Dataverse – Fabric Link: Metadata Tables No Longer Synced

Fabric Dataverse - Synapse Link versus Fabric Link