# Problem in DAX

### Question

• Hello,

I encountered an issue when trying to use SUMMARIZE to get the sum.

Model was simplified as the followings:

1) Three tables: CalculatedTable 1, CalculatedTable 2, ContractDate. Kindly note that the date format was DD/MM/YYYY.

CalculatedTable 1:

Row1   Row2   Row3

A         1         1/1/2018

A         2         1/2/2018

B         3         2/1/2018

B         4         2/2/2018

CalculatedTable 2:

Row1   Datetest

A         1/1/2017

B         1/2/2017

ContractDate: this is the date table.

2. Relationships:

CaculatedTable 1 [Row1]  *:1 CalculatedTable 2[Row1]

CalculatedTable 2 [Datetest]  *:1  ContractDate [Datekey]

3. Measure:

`TEST S :=CALCULATE( SUMX( SUMMARIZE( 'CalculatedTable 1',                             'CalculatedTable 1'[Row1],                             "SUM1",                              SUMX( FILTER( 'CalculatedTable 1',                                    'CalculatedTable 1'[Row3] >= MIN( ContractDate[FullDate])                                    && 'CalculatedTable 1'[Row3] <= MAX(ContractDate[FullDate])),                                   'CalculatedTable 1'[Row2]),                            "SUM2",                            SUMX( FILTER( 'CalculatedTable 1',                                           'CalculatedTable 1'[Row3] > MAX( ContractDate[FullDate])),                                          'CalculatedTable 1'[Row2])),                  [SUM1] - [SUM2]),           ALLEXCEPT('CalculatedTable 1', 'CalculatedTable 1'[Row1]))`

Expected Result:

When selecting the month from the slicer,  it returns the sum of the value in CalculatedTable 1 for the rows which satisfy the conditions in the measure.

e.g. When I select 2018-01,

Row Labels   TEST S

A                  -1

B                  -1

Current Result:

Unexpected values were returned.

Row Labels   TEST S

A                  -3

B                  -7

Current Finding:

The issue seems to be related to the relationship between CalculatedTable 2 and ContractDate. When the relationship was removed, the result was correct.

However, the relationship cannot be removed from the real data model.

I would like to seek for your help to understand:

1. why the relationship affected the result .

2. if there is a way to resolve it.

Hope the information above is clear enough. I am sorry that I cannot uploaded screenshots for my account hasn't been verified yet.

• Edited by Thursday, June 14, 2018 10:32 AM edit
Thursday, June 14, 2018 10:30 AM

### All replies

Friday, June 15, 2018 8:33 AM
• Hi Rhea.L,

Thank you for the update

Can you please post the solution in this thread and mark that as answer. In this way other people in the forum will get benefited if they have a similar issue.

Thanks for understanding.

HTH,

Ram

Friday, June 15, 2018 9:14 AM
• Hi Rhea.L,