Forum Discussion
CALCULATETABLE with filter from multiple table
Hi ,
Am having a scenario where a client can purchase classes for multiple students. Once the purchase is successful student will admitted to a class
Tables:
Invoice ==> InvoiceID, Total amount
Class ==> Class ID, COURSEID
Class Students==> ClassId, Students list (One to many rom CLass), Invoice ID(Many to One to the Invoice)
invoice Lines ==> invoiceId (One to many), CourseId (NOT able to link to CLass CourseId) throwing error as "there are ambiguous paths", quantity
Am looking to have the price paid for each student. May I know the best approach. Any help is appreciated
Hi albypeter ,
One approach is to create a calculated table that does not rely on relationships between the tables. Here is an example of a calculated table for your use case:PricePaid = SUMMARIZECOLUMNS( 'Class Students'[StudentID], "TotalPaid", CALCULATE( SUMX( 'Invoice Lines', 'Invoice Lines'[Quantity] * 'Invoice Lines'[UnitPrice] ), TREATAS ( VALUES('Class Students'[InvoiceID]), 'Invoice Lines'[InvoiceID]) ) )
Please let me know if this achieves your desired result.If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
Connect with me on LinkedIn
Check out my Blog
Going to the European Microsoft Fabric Community Conference? Check out my Session
Thnak you, will try the solution and update soon
Hi albypeter ,
As per the original post, I tried to implement the solution. Please find the pbix file.
CALCULATETABLE with filter from multiple table.pbixBest Practices:
1.Avoid ambiguous relationships by using DAX instead of physical relationships.
2.Use TREATAS or LOOKUPVALUE to simulate joins.
3.Use calculated measures to dynamically compute per-student pricing.Please let me know if there is any missing things in the pbix file.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
7 Replies
Your problem likely comes from trying to join:
- InvoiceLines.CourseID → Class.CourseID
- While ClassStudents already connects InvoiceID → Invoice → InvoiceLines
This creates two paths from InvoiceLines to Class, and Power BI throws the "ambiguous paths" error.
Relational Setup (Star Schema-like)
Table
Relationship
Invoice → InvoiceLines
1-to-many
Invoice → ClassStudents
1-to-many
Class → ClassStudents
1-to-many
Class → Course
many-to-1
InvoiceLines → Course
many-to-1
Do NOT link InvoiceLines.CourseID to Class.CourseID directly if it causes ambiguity. Instead:
- Create a Course dimension table to sit between them.
- Or use DAX LOOKUPs instead of physical relationships when ambiguity exists.
Once relationships are cleaned up:
Approach 1: If Each Line Represents 1 Course x N Students
Student Unit Price =
DIVIDE (
SUM(InvoiceLines[Amount]),
SUM(InvoiceLines[Quantity])
)
Then join this back to ClassStudents using the common InvoiceID and possibly CourseID (if clean).
Alternate Approach: Add CourseID to ClassStudents
If a student can only be admitted to one course per invoice line, enhance ClassStudents:
- Add CourseID to it
- Then you can cleanly link:
ClassStudents[CourseID] → InvoiceLines[CourseID]
ClassStudents[InvoiceID] → InvoiceLines[InvoiceID]
Result: a clear link per student → invoice line
Then:
Student Unit Price =
CALCULATE (
DIVIDE (
SUM(InvoiceLines[Amount]),
SUM(InvoiceLines[Quantity])
),
FILTER (
InvoiceLines,
InvoiceLines[InvoiceID] = ClassStudents[InvoiceID] &&
InvoiceLines[CourseID] = ClassStudents[CourseID]
)
)
Thnak you, will try the solution and update soon
Hi albypeter ,
One approach is to create a calculated table that does not rely on relationships between the tables. Here is an example of a calculated table for your use case:PricePaid = SUMMARIZECOLUMNS( 'Class Students'[StudentID], "TotalPaid", CALCULATE( SUMX( 'Invoice Lines', 'Invoice Lines'[Quantity] * 'Invoice Lines'[UnitPrice] ), TREATAS ( VALUES('Class Students'[InvoiceID]), 'Invoice Lines'[InvoiceID]) ) )
Please let me know if this achieves your desired result.If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
Connect with me on LinkedIn
Check out my Blog
Going to the European Microsoft Fabric Community Conference? Check out my Session
Thank you, will try the solution suggested and confirm
Hi,
Share some data to work with and show the expected result. Either share the download link of the PBI file or share data in a format that can be pasted in an MS Excel file.
Hi albypeter ,
As per the original post, I tried to implement the solution. Please find the pbix file.
CALCULATETABLE with filter from multiple table.pbixBest Practices:
1.Avoid ambiguous relationships by using DAX instead of physical relationships.
2.Use TREATAS or LOOKUPVALUE to simulate joins.
3.Use calculated measures to dynamically compute per-student pricing.Please let me know if there is any missing things in the pbix file.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
Thank you all, used a combination of
TREATAS
LOOKUPVALUEDimensionTableAppreciate your help
