CALCULATETABLE with filter from multiple table

[albypeter]

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

[SamsonTruong · Accepted solution]

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

[albypeter · Accepted solution]

Thnak you, will try the solution and update soon

[albypeter]

Thank you all, used a combination of 

TREATAS

LOOKUPVALUE

DimensionTable

Appreciate your help

[maruthisp · Accepted solution]

Hi @albypeter ,

As per the original post, I tried to implement the solution. Please find the pbix file.
CALCULATETABLE with filter from multiple table.pbix

Best 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 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[Ashish_Mathur]

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.

[SamsonTruong · Accepted solution]

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

[albypeter]

Thank you, will try the solution suggested and confirm

[Shravan133]

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]

    )

)

[albypeter · Accepted solution]

Thnak you, will try the solution and update soon