Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |