none
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.

    Thank you in advance.


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

All replies

  • This thread can be closed. Already found a workaround.

    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


    Please vote as helpful or mark as answer, if it helps

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

    Thanks for your question.

    Just like Ram said, to close this thread, please share the solution and mark it as an answer. By doing so, it will benefit all community members who are facing this similar issue. Your contribution is highly appreciated.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 18, 2018 1:12 AM
    Moderator