cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
danimelv Helper II
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:

 

Duda SS.png 

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:

 

TypeM.png

 

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

sumRes.png

How can I get the right total?

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
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.

View solution in original post

3 REPLIES 3
Community Support
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.

Expanded tables in DAX 

 

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.

danimelv Helper II
Helper II

Re: Table expansion measure

Hi @v-xuding-msft 

 

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

 

Thanks in advance!

Super User III
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.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

Community Summit North America

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

Top Solution Authors