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

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
Frequent Visitor

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
Frequent Visitor

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors