cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
optimusprime Frequent Visitor
Frequent Visitor

Complicated Table Links

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
v-huizhn-msft Super Contributor
Super Contributor

Re: Complicated Table Links

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


Please download the file for more details.

Best Regards,
Angelia

2 REPLIES 2
optimusprime Frequent Visitor
Frequent Visitor

Complicate table linkage

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.

 

v-huizhn-msft Super Contributor
Super Contributor

Re: Complicated Table Links

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


Please download the file for more details.

Best Regards,
Angelia