Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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"
) )
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 )
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.
@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.
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"
) )
@AlB If the result is neither >20% above or <20% below, I would like to enter "Null".
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |