Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Transformed Table Measure

Hi Expert,   I would like to have 2 measures that to transform table   Raw data is like this   Customer Code Item Amount A 01 MM 100 A 01 NN 200 A 02 ZZ 300 B 01 MM...
DAX. CALCULATE
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Anonymous ,

    Ok then! Added a column to check on whether the customer had 1. Here is my pbix. Count of 1 and 2

     

    Sum of 2 = 

var _code = 2
var _calc = CALCULATE([Sum of Amount],'Count'[Code]=2,'Count'[IF Customer has 1]>=1)


return _calc

     


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi Anonymous ,

    This is the new Sum of 2, which does not need the Calculated Column.  Had to go to another guy on the leaderboards jdbuchanan71 .

    Whew!


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

    Sum of 2 =
VAR _CustList =
    CALCULATETABLE (
        INTERSECT (
            CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 ),
            CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2 )
        ),
        ALLEXCEPT ( 'Count', 'Count'[Customer] )
    )
RETURN
    CALCULATE (
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList ),
        KEEPFILTERS ( 'Count'[Code] = 2 )
    )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

May I ask further, It is possible that not create a column to do so?

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi Anonymous ,

This is the new Sum of 2, which does not need the Calculated Column.  Had to go to another guy on the leaderboards jdbuchanan71 .

Whew!


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

Sum of 2 =
VAR _CustList =
    CALCULATETABLE (
        INTERSECT (
            CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 ),
            CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2 )
        ),
        ALLEXCEPT ( 'Count', 'Count'[Customer] )
    )
RETURN
    CALCULATE (
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList ),
        KEEPFILTERS ( 'Count'[Code] = 2 )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Perfect, exactly what I want, thanks so much

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Nathaniel_C 


    This is equation I modified.

    Sum of 2 (New) = 
VAR _CustList =
    CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 )
RETURN
    CALCULATE ( 
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList ),
        'Count'[Code] = 2)

    I'm so curious why it's empty for (ZZ, Sum of 2 (New) ) and the total is correct, it's very very strange. how come?

     

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      Hi Anonymous ,

      We ran into the same thing.

      Here is the first solution and the subsequent solution that jdbuchanan71 sent.  The first one had exactly the same issue. Not sure why. 

      First one:
Measure = 
VAR _CustList = 
    INTERSECT (
        CALCULATETABLE (
            DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1),
        CALCULATETABLE (
            DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2)
    )
RETURN
    CALCULATE ( 
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList ),
        KEEPFILTERS( 'Count'[Code] = 2 )
    ) 

Second one:
Measure = 
VAR _CustList = 
CALCULATETABLE(
    INTERSECT (
        CALCULATETABLE (
            DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1),
        CALCULATETABLE (
            DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2)
    )
    ,ALLEXCEPT('Count','Count'[Customer])
)
RETURN
    CALCULATE ( 
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList )
        ,KEEPFILTERS( 'Count'[Code] = 2 )
    )

      Nathaniel

    • jdbuchanan71's avatar
      jdbuchanan71
      Super User
      6 years ago

      Like all measures that act differently than we think it should it's all about filter context. If we take the line for Item NN as an example:
      The first part of your measure

      VAR _CustList =
    CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 )

      gets the filter context of [Item] = NN (from the line in the visual) and you add the filter [Code] = 1. This returns a list of customers, in this case A, B, C (those are the only customers with [Item] NN and [Code] 1.

      Then the second line

          CALCULATE ( 
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList ),
        'Count'[Code] = 2)

      Calculates the SUM and applies 3 filters.
      1. The [Item] filter from the visual of NN
      2. The list from VAR _CustList: (A, B, C)
      3. [Code] = 2 which only appears on lines for customers (D, E)

      So you are asking it to SUM ( 'Count'[Amount] ) for customers (A, B, C) from these lines:

      Customer Code Item Amount
      E 2 NN 1400
      D 2 NN 1100

      No lines match all the criteria so we get a blank.