cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Table expansion measure

This is an example of the model I have. All filters direction across tables are setted as bidirectional in order to trying to get work my measure:

From TypeM table I want to show the SumAmt. The problem is when I'm trying to show the SumAmt by date.

I'm dealing with table expansion but I can't make it work. Reading this book, as Marco and Alberto Pointed "from the farthest table from the fact table to the nearest one", I'm using this measure:

``````Sum of Amount= CALCULATE(  SUM(TypeM[SumAmt]); CALCULATETABLE(TypeM;Calendar;Delegat;Customer;Solution)
)``````

But I'm not having the expected result:

Having selected Model1 for simplicity, I'm getting this:

How can I get the right total?

Edit: I'm attaching a sample PBIX: This PBIX

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

## Re: Table expansion measure

@danimelv wrote:

At the end, the expected result in the last table should be:

MonthName| Year | Sum of Amt

Sept               2019            5

Feb                 2020            5

Total             |                    10

Your data model does not look correct to try and get that sort of result.

Your date is linked to the amount through a many to many (m2m) relationship. The typical example of a m2m relationship is a joint bank account. If you have a husband and wife with a joint bank account and there is \$10 in that account when you list the account balance by person you would see the following:

Husband  \$10

Wife         \$10

Total        \$10

So the huband and wife cannot both spend \$10, if the husband spends \$10 there will be 0 left in the account.

This is the way you have modelled your dates against the TypeM table. If you are saying that Model1 had \$5 of sales in Sep 19 and \$5 sales in Feb 2020. Then there should be a DateID column in the TypeM table and a row for each month against Model1. If you do this then the 2 months will naturally aggregate and you will get a total of \$10.

3 REPLIES 3
Community Support

## Re: Table expansion measure

Hi @danimelv ,

Can you please show the expected results? You could reference the blog firstly. Hope it can help you.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper II

## Re: Table expansion measure

Thanks for the reference. The problem is that is not possible to reference from calendar table to the other table with an inactive relation. At the end, the expected result in the last table should be:

MonthName| Year | Sum of Amt

Sept               2019            5

Feb                 2020            5

Total             |                    10

Super User III

## Re: Table expansion measure

@danimelv wrote:

At the end, the expected result in the last table should be:

MonthName| Year | Sum of Amt

Sept               2019            5

Feb                 2020            5

Total             |                    10

Your data model does not look correct to try and get that sort of result.

Your date is linked to the amount through a many to many (m2m) relationship. The typical example of a m2m relationship is a joint bank account. If you have a husband and wife with a joint bank account and there is \$10 in that account when you list the account balance by person you would see the following:

Husband  \$10

Wife         \$10

Total        \$10

So the huband and wife cannot both spend \$10, if the husband spends \$10 there will be 0 left in the account.

This is the way you have modelled your dates against the TypeM table. If you are saying that Model1 had \$5 of sales in Sep 19 and \$5 sales in Feb 2020. Then there should be a DateID column in the TypeM table and a row for each month against Model1. If you do this then the 2 months will naturally aggregate and you will get a total of \$10.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors