cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CliffordAP
Helper I
Helper I

Measure to Calculate a sum across several related tables (Model/DAX)

Hello all,

I have a small puzzle I am trying to understand. I have a relational database where GLs are attributed to products and services.

Here is the model I have so far, and the relationships I have modeled:

CliffordAP_0-1638378172747.png

CliffordAP_1-1638378204159.png

The best I can do with this setup is the following visual:

CliffordAP_2-1638378275045.png

Since the balances for products and services are not on the same table, I cannot select them into the same column aligned with their respective GLs. Is there a way I can create a measure that will put these calculations into the same column?

What I have tried so far:

CliffordAP_4-1638378526313.png

Here I attempted to see if it would only show the sum across the products and not the services. But it bloated everything instead.


I am looking for something like:

CliffordAP_3-1638378453308.png

Is there a way I can accomplish this with a DAX measure? or is there some alternative method I can try in the model?
     *I would like to keep the product and service tables seperate from one another

     *I would like to attach the .pbix file with the model already in it, but I don't know how.
UPDATE:

CliffordAP_1-1638381041087.png

 

I can get it to do exactly what I want in DAX Studio, but the DAX query does not have the same output in the desktop application. 

Thank you!

1 ACCEPTED SOLUTION
CliffordAP
Helper I
Helper I

The filter context was activated in DAX Studio because the table argument was explicitly defined in the query. In Desktop it is not. Thus:

CALCULATE(SUM('Product'[Balance])+SUM('Service'[Balance]),'glFactTable')
Will work in PBI Desktop.

CliffordAP_0-1638466398987.png

Thank you me!

Your Welcome.. Me.

 

View solution in original post

1 REPLY 1
CliffordAP
Helper I
Helper I

The filter context was activated in DAX Studio because the table argument was explicitly defined in the query. In Desktop it is not. Thus:

CALCULATE(SUM('Product'[Balance])+SUM('Service'[Balance]),'glFactTable')
Will work in PBI Desktop.

CliffordAP_0-1638466398987.png

Thank you me!

Your Welcome.. Me.

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!