Forum Discussion
Create new conditional column
Hello, can someone please help me with DAX?
I want to create a fourth column called "Satatus" where I indicate if the "ID", "Year", "activity" had the same activity. Then the result in the new column "Status" would say: Same, Changed, None
Thanks a lot!! Rosario Pineda.
| ID | Year | Activity |
| 1 | 2019 | Yoga |
| 1 | 2019 | Dance |
| 1 | 2019 | Pilates |
| 1 | 2019 | Swimming |
| 1 | 2020 | Dance |
| 1 | 2020 | Pilates |
| 1 | 2020 | Swimming |
| 1 | 2020 | Meditation |
| 2 | 2019 | Yoga |
| 2 | 2019 | Dance |
| 2 | 2019 | Pilates |
| 2 | 2019 | Swimming |
| 2 | 2020 | Yoga |
| 2 | 2020 | Dance |
| 2 | 2020 | Pilates |
| 2 | 2020 | Swimming |
| 3 | 2019 | Yoga |
| 3 | 2019 | Dance |
| 3 | 2019 | Pilates |
| 3 | 2019 | Swimming |
| 3 | 2020 | Yoga |
| 3 | 2020 | Dance |
| 3 | 2020 | |
| 3 | 2020 | Swimming |
you can try this
Column = VAR _a=maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Year]<>EARLIER('Table'[Year])&&'Table'[Activity]=EARLIER('Table'[Activity])),'Table'[Activity]) return if('Table'[Activity]="","Ninguna",if(_a="","Cambiaron","Misma"))pls see the attachment below
@ryan_mayu thank you very much for your support, I work percfecto!!
Greetings and blessings. Rosary.
6 Replies
i can't find id year and activity has the same value.
could you pls add the expected output in the sample data you provided?
Sorry I didn't understand your question, I rewuiero new column as the example below:
you can try this
Column = VAR _a=maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Year]<>EARLIER('Table'[Year])&&'Table'[Activity]=EARLIER('Table'[Activity])),'Table'[Activity]) return if('Table'[Activity]="","Ninguna",if(_a="","Cambiaron","Misma"))pls see the attachment below
Not sure this is what you mean, but please try this column expression. Replace Activities with your actual table name.
NewColumn =
VAR thisactivity = Activities[Activity]
VAR thisyear = Activities[Year]
VAR PYactivities =
CALCULATETABLE (
DISTINCT ( Activities[Activity] ),
ALLEXCEPT ( Activities, Activities[ID] ),
Activities[Year] = thisyear - 1
)
RETURN
SWITCH (
TRUE (),
ISEMPTY ( PYactivities ), "None",
thisactivity IN PYactivities, "Same",
"Change"
)Pat
