Forum Discussion

pjr1221's avatar
pjr1221
Frequent Visitor
7 years ago
Solved

Add Column with DAX function

Hello All, 

 

How can I add column using DAX? I don't know what function to use in DAX. 

Here is sample table,I can't show my real table because it's too big.

 

 

 

 

 

 

 

 

 

 

 

 

I want add <GAP> column.

The formla is in the same <ID>, add differences value compared with previous week value.

below table is what i looking for table.

 

It is too difficult for me.. i'm beginner 
Please help me.

 

 

 

 

 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    pjr1221 

    Edited:

    I took the last line away. That was causing the variant data type error. You haven't described what you need if the result is neither >20% above or <20% below. The code below returns blank in those cases. If that's no what you need update it accordingly.

     

    GAP_v2 =
VAR _ValuePreviousWeek =
    CALCULATE (
        DISTINCT ( Table1[Value] );
        ALLEXCEPT ( Table1; Table1[ID] );
        Table1[WEEK]
            = EARLIER ( Table1[WEEK] ) - 1
    )
RETURN
    IF (NOT ISBLANK ( _ValuePreviousWeek );
        VAR _Perc =
            DIVIDE ( Table1[VALUE] - _ValuePreviousWeek; _ValuePreviousWeek )
        RETURN
            SWITCH (
                TRUE ();
                _Perc >= 120 / 100; "Increase";
                _Perc <= 80 / 100; "Decrease"
                )
    )

     

6 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi pjr1221 

     

    Try this for your new calculated column. Table1 is the name of the table you show

     

     

    GAP =
VAR _ValuePreviousWeek =
    CALCULATE (
        DISTINCT ( Table1[Value] );
        ALLEXCEPT ( Table1; Table1[ID] );
        Table1[WEEK] = EARLIER ( Table1[WEEK] ) - 1
    )
RETURN
    IF ( NOT ISBLANK ( _ValuePreviousWeek ); Table1[VALUE] - _ValuePreviousWeek )

     

    On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.

     

     

    • pjr1221's avatar
      pjr1221
      Frequent Visitor
      7 years ago

      AlB Thank you so much, AIB. In addition, if the difference is increased by more than 20%, I would like to enter "Increase", if it decreases by more than 20%, I would like to enter "Decrease". but what should I do?

      Thank you very much for your help.

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        pjr1221 

        Edited:

        I took the last line away. That was causing the variant data type error. You haven't described what you need if the result is neither >20% above or <20% below. The code below returns blank in those cases. If that's no what you need update it accordingly.

         

        GAP_v2 =
VAR _ValuePreviousWeek =
    CALCULATE (
        DISTINCT ( Table1[Value] );
        ALLEXCEPT ( Table1; Table1[ID] );
        Table1[WEEK]
            = EARLIER ( Table1[WEEK] ) - 1
    )
RETURN
    IF (NOT ISBLANK ( _ValuePreviousWeek );
        VAR _Perc =
            DIVIDE ( Table1[VALUE] - _ValuePreviousWeek; _ValuePreviousWeek )
        RETURN
            SWITCH (
                TRUE ();
                _Perc >= 120 / 100; "Increase";
                _Perc <= 80 / 100; "Decrease"
                    )
    )

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    GAP =
    VAR _ValuePreviousWeek =
        CALCULATE (
            DISTINCT ( Table1[Value] );
            ALLEXCEPT ( Table1; Table1[ID] );
            Table1[WEEK] = EARLIER ( Table1[WEEK] ) - 1)
    RETURN
        IF ( NOT ISBLANK ( _ValuePreviousWeek ); Table1[VALUE] - _ValuePreviousWeek )