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 )
    )
Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
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

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
    Icon for Community Champion rankCommunity 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
        Icon for Community Champion rankCommunity 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