cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pjr1221 Frequent Visitor
Frequent Visitor

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.

test11.PNG

 

 

 

 

 

 

 

 

 

 

 

 

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.

test.PNG

 

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Add Column with DAX function

@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"
) )

 

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Add Column with DAX function

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 Frequent Visitor
Frequent Visitor

Re: Add Column with DAX function

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

Highlighted
Super User
Super User

Re: Add Column with DAX function

@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"
) )

 

View solution in original post

pjr1221 Frequent Visitor
Frequent Visitor

Re: Add Column with DAX function

Hi, @AlB  I've applied it to my data, it's not working.

 

error is :

Expressions that yield variant data-type cannot be used to define calculated columns.

pjr1221 Frequent Visitor
Frequent Visitor

Re: Add Column with DAX function

@AlB  If the result is neither >20% above or <20% below, I would like to enter "Null".

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020